Option Explicit On

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Text


Public Class Reports

    Public Enum ReportType
        Commission = 111
        InvoiceList = 112
        InvoiceListV2 = 113
        CashUp = 114
        EventSummaryConsultant = 115
        PriceList = 116
        CaseStages = 117
        PipelinePreSale = 118
        SalesStatusSummary = 119
        VisasExpiring = 120
        OpenCasesNoInvoice = 121
        EnteredBySummary = 122
        MigrationCaseStages = 123
        LeadsPerConsultant = 124
        WebLeadsPerDay = 125
        WebSalesPerDay = 126
        WebLeadsAssignedToSalespeople = 127
        ConversionByConsultant = 128
        ConversionRateProduct = 129
        'LeadsAllocated = 130
        WriteOffList = 130
        WorkInProgress = 131
        CaseWithNoEvents = 132
        CaseWithOutstandingBalance90 = 133
        UserPipeline = 134
        CasesSubmitted = 135
        CasesAssigned = 136
        CaseWithOutstandingBalance60 = 137
        FeedbackReport = 138
        DailySalesReport = 139
        WeeklySalesReport = 140
        MonthlySalesReport = 141
        LeadsReportByProduct = 142
        PaymentDue30 = 143
        PaymentDue60 = 144
        PaymentDue90 = 145
        FeedbackCanReport = 146
        PaymentDue30SLA = 147
        PaymentDue60SLA = 148
        PaymentDue90SLA = 149
        RefundsList = 150
        CSMCaseStage = 151
        InvoiceListCase = 152
        CaseAssignedCSM = 153
        LeadsProductType = 154
    End Enum

    Public Enum ConsultantRoles
        SoldBy = 1
        CaseWorker = 2
    End Enum

    Public Enum ProductSelection
        All = 1
        By_Department = 2
        Retired_Products = 3
        By_Product_Type = 4
    End Enum

    Public Enum AccountTypes
        All = 1
        SLA = 2
        ExcludeSLA = 3
    End Enum

    Public Enum ClientTypes
        All = 1
        PrivateOnly = 2
        CorporateOnly = 3
    End Enum

    Public Structure ReportCriteria
        'Used in SQL
        '***********************
        Public Consultant_All As Boolean
        Public Consultant_Id As Integer
        'Public Consultant_DeptId As Integer
        Public Consultant_BranchId As Integer
        Public Consultant_Role As ConsultantRoles
        Public StartDate As String
        Public EndDate As String
        Public Product_Selection As ProductSelection
        Public Product_Selection_Id As Object 'Varies depending on whether department, type
        Public Account_Types As AccountTypes
        Public Client_Type As ClientTypes
        '***********************
        'Used for formula fields in report
        '************************
        Public Period As String
        Public Consultant_Name As String
        Public Product_Name As String
        '************************
    End Structure


    Public Function GetReportData(ByVal ReportId As ReportType) As DataTable

        Dim SQL As String = ""
        Dim myData As DataTable
        Dim dr As DataRow
        Dim myobj As Object
        Dim i As Integer
        Dim currency As String
        Select Case ReportId

            Case ReportType.Commission
                SQL = Me.SQL_Commission

            Case ReportType.InvoiceList
                Dim arrSQL() As String = SQL_InvoiceList()
                'SQL = Me.SQL_InvoiceList
                myData = GenerateInvoiceReportByConsul(arrSQL(0), arrSQL(1))
                Return myData

            Case ReportType.InvoiceListV2
                SQL = Me.SQL_InvoiceListV2
                myData = GenerateInvoiceReportByConsul_old(SQL)
                Return myData
            Case ReportType.CashUp


                SQL = Me.SQL_CashUp
                myData = Data.GetQueryResults(SQL)
                myData.Columns.Add("Payment_enteredBy")
                For Each dr In myData.Rows
                    myobj = Me.SQL_CashUPConsultant(dr.Item("Payment_caseID"))
                    dr.BeginEdit()
                    dr.Item("Payment_enteredBy") = myobj.ToString
                    dr.EndEdit()
                Next
                Return myData
            Case ReportType.EventSummaryConsultant
                SQL = Me.SQL_EventSummaryConsultant

            Case ReportType.PriceList
                SQL = Me.SQL_PriceList

            Case ReportType.CaseStages
                SQL = Me.SQL_CaseStages

            Case ReportType.PipelinePreSale
                SQL = Me.SQL_PipeLinePreSale

            Case ReportType.SalesStatusSummary
                SQL = Me.SQL_SalesStatusSummary

            Case ReportType.VisasExpiring
                SQL = Me.SQL_VisasExpiring

            Case ReportType.OpenCasesNoInvoice
                SQL = Me.SQL_OpenCasesNoInvoice

            Case ReportType.EnteredBySummary
                SQL = Me.SQL_EnteredBySummary

            Case ReportType.MigrationCaseStages
                SQL = Me.SQL_MigrationCaseStages

            Case ReportType.LeadsPerConsultant
                SQL = Me.SQL_LeadsPerConsultant

            Case ReportType.WebLeadsPerDay
                SQL = Me.SQL_WebLeadsPerDay(Me.Criteria.StartDate, Me.Criteria.EndDate)

            Case ReportType.WebSalesPerDay
                SQL = Me.SQL_WebSalesPerDay(Me.Criteria.StartDate, Me.Criteria.EndDate)

            Case ReportType.WebLeadsAssignedToSalespeople
                SQL = Me.SQL_WebLeadsAssignedToSalespeople(Me.Criteria.StartDate, Me.Criteria.EndDate)

            Case ReportType.ConversionByConsultant
                Dim arrSQL() As String = SQL_ConversionByConsultant()

                myData = GenerateConversionConReport(arrSQL(0), arrSQL(1))

                Return myData

            Case ReportType.ConversionRateProduct
                Dim arrSQL() As String = SQL_ConversionByProduct()

                myData = GenerateConversionProdReport(arrSQL(0), arrSQL(1))

                Return myData
                'Case ReportType.LeadsAllocated
                'SQL = Me.SQL_LeadsAllocated

                'Case ReportType.InvoiceList
                '    SQL = Me.SQL_InvoiceList
                '    myData = GenerateWriteOffReportByConsul(SQL)
                '    Return myData


            Case ReportType.WriteOffList
                SQL = Me.SQL_InvoiceList1
                myData = GenerateWriteOffReportByConsul(SQL)
                Return myData

                
            Case ReportType.WorkInProgress
                SQL = Me.SQL_WorkInProgress
                myData = Data.GetQueryResults(SQL)
                For Each dr In myData.Rows
                    If dr.Item("Company_name").ToString = "" Then
                        'currency = GetCurrency(dr.Item("Case_ID"))
                        dr.BeginEdit()
                        dr.Item("Company_name") = dr.Item("Candidate")
                        dr.EndEdit()
                    End If

                Next
                Return myData


            Case ReportType.CaseWithNoEvents
                SQL = Me.SQL_NoEventCases()
            Case ReportType.CaseWithOutstandingBalance90
                SQL = Me.SQL_OverdueBalance_90()

            Case ReportType.CaseWithOutstandingBalance60
                SQL = Me.SQL_OverdueBalance_60()

            Case ReportType.UserPipeline
                SQL = Me.SQL_SalePipeline()
                'SQL = Me.SQL_UserPipeline()
            Case ReportType.CasesSubmitted
                SQL = Me.SQL_casesSubmitted()
            Case ReportType.CasesAssigned
                SQL = SQL_CasesAssigned()
            Case ReportType.FeedbackReport
                SQL = SQL_FeedbackReport()
            Case ReportType.FeedbackCanReport
                SQL = SQL_FeedbackReport_CancelledPresale()
            Case ReportType.DailySalesReport
                SQL = SQL_DailySalesReport()
                myData = GenerateInvoiceReportByConsul_old(SQL)
                Return myData
            Case ReportType.WeeklySalesReport
                SQL = SQL_WeeklySalesReport()
                myData = GenerateInvoiceReportByConsul_old(SQL)
                Return myData
            Case ReportType.MonthlySalesReport
                SQL = SQL_MonthlySalesReport()
                myData = GenerateInvoiceReportByConsul_old(SQL)
                Return myData
            Case ReportType.LeadsReportByProduct
                SQL = SQL_LeadsByProduct()
            Case ReportType.PaymentDue30
                SQL = SQL_PaymentDue(30)
            Case ReportType.PaymentDue60
                SQL = SQL_PaymentDue(60)
            Case ReportType.PaymentDue90
                SQL = SQL_PaymentDue(90)

            Case ReportType.PaymentDue30SLA
                SQL = SQL_PaymentDueSLA(30)
            Case ReportType.PaymentDue60SLA
                SQL = SQL_PaymentDueSLA(60)
            Case ReportType.PaymentDue90SLA
                SQL = SQL_PaymentDueSLA(90)


            Case ReportType.RefundsList
                SQL = Me.SQL_Refunds(Me.Criteria.StartDate, Me.Criteria.EndDate)
            Case ReportType.CSMCaseStage
                SQL = Me.SQL_CSMCaseStage(Me.Criteria.StartDate, Me.Criteria.EndDate)
            Case ReportType.InvoiceListCase
                SQL = Me.SQL_InvoiceListCase
                myData = GenerateInvoiceReportByConsul_old(SQL)
                Return myData
            Case ReportType.CaseAssignedCSM
                SQL = SQL_CaseAssignedCSM()
            Case ReportType.LeadsProductType
                SQL = SQL_LeadsProductType()
        End Select

        Data.OpenDB()

        myData = Data.GetQueryResults(SQL)

        Data.CloseDB()

        'myData.WriteXmlSchema("c:\crap\WebLeads.xsd")

        Return myData

       

    End Function

    Private Function SQL_UserPipeline()
        Dim SQL As New StringBuilder
        With SQL
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(" SELECT     TOP 100 PERCENT dbo.D_Case.Case_ID AS case_Id, ISNULL(D_Person_Candidate.Person_LName + ', ' + D_Person_Candidate.Person_FName, ")
                .Append(" 'Unspecified') AS Candidate, ISNULL(dbo.L_CaseStages.CaseStage_Title, 'Unspecified') AS Case_stage, ")
                .Append(" dbo.L_Products.Product_Name AS Prod_Name, ISNULL(dbo.L_ProductTypes.ProductType_Name, '') AS Prod_type, ")
                .Append(" CASE WHEN Case_ActiveStatus = 1 THEN 'Active' ELSE 'Inactive' END AS ActiveStatus, dbo.L_Consultant.Con_Name, ")
                .Append(" CASE WHEN Case_CompanyID = 0 THEN 'Private Individual' ELSE 'Company' END AS IsCompany")
                .Append(" FROM         dbo.D_Case INNER JOIN")
                .Append(" dbo.L_Consultant ON dbo.D_Case.Case_ConSaleID = dbo.L_Consultant.Con_ID LEFT OUTER JOIN")
                .Append(" dbo.D_Person AS D_Person_Candidate ON dbo.D_Case.Case_CandidateID = D_Person_Candidate.Person_ID LEFT OUTER JOIN")
                .Append(" (SELECT     Event_LinkID, CONVERT(varchar(12), MAX(Event_DTEntered), 103) AS LastEvent, CONVERT(varchar(12), ")
                .Append(" MIN(CASE Event_Category WHEN 2 THEN Event_DTScheduled ELSE NULL END), 103) AS NextTask, CONVERT(varchar(12), ")
                .Append(" MAX(CASE Event_Category WHEN 3 THEN Event_DTCompleted ELSE NULL END), 103) AS LastComp, COUNT(Event_ID) AS TotalEvents, ")
                .Append(" SUM(CASE Event_Category WHEN 2 THEN 1 WHEN 3 THEN 1 ELSE 0 END) AS TotalTasks, ")
                .Append(" SUM(CASE Event_Category WHEN 2 THEN 1 ELSE 0 END) AS TotalSched, SUM(CASE Event_Category WHEN 3 THEN 1 ELSE 0 END) ")
                .Append(" AS TotalComp, SUM(CASE WHEN Event_Category = 2 AND Event_DTScheduled < getdate() THEN 1 ELSE 0 END) AS TotalOS")
                .Append(" FROM dbo.D_Event")
                .Append(" WHERE(Event_LinkType = 1)")
                .Append(" GROUP BY Event_LinkID) AS D_Event_Case ON dbo.D_Case.Case_ID = D_Event_Case.Event_LinkID LEFT OUTER JOIN")
                .Append(" dbo.L_CaseStages ON dbo.D_Case.Case_StatusID = dbo.L_CaseStages.CaseStage_Number LEFT OUTER JOIN")
                .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
                .Append(" (SELECT     EMailAddress_Address, EMailAddress_PersonID")
                .Append(" FROM dbo.D_EmailAddress")
                .Append(" WHERE      (EMailAddress_Type = 1)) AS D_EMailAddressCandidate_Main ON ")
                .Append(" D_Person_Candidate.Person_ID = D_EMailAddressCandidate_Main.EMailAddress_PersonID LEFT OUTER JOIN")
                .Append(" dbo.L_Country AS L_Country_CandRes ON D_Person_Candidate.Person_ResidentID = L_Country_CandRes.Country_ID LEFT OUTER JOIN")
                .Append(" dbo.L_Products AS L_Products_TypeLink ON dbo.D_Case.Case_ProductID = L_Products_TypeLink.Product_ID LEFT OUTER JOIN")
                .Append(" dbo.L_ProductTypes ON L_Products_TypeLink.Product_Type = dbo.L_ProductTypes.ProductType_ID LEFT OUTER JOIN")
                .Append(" dbo.L_Country AS L_Country_CandNat ON D_Person_Candidate.Person_NatID = L_Country_CandNat.Country_ID")
                .Append(" WHERE     (dbo.D_Case.Case_StatusID < 1000) ")
                .Append(Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConSaleID"))
                .Append(" ORDER BY dbo.L_Consultant.Con_Name, dbo.D_Case.Case_StatusID, dbo.D_Case.Case_ID, ")
                .Append(" CASE WHEN Case_ActiveStatus = 1 THEN 'Active' ELSE 'Inactive' END")

            End If
            If Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append("SELECT     TOP 100 PERCENT dbo.D_Case.Case_ID, ISNULL(D_Person_Candidate.Person_LName + ', ' + D_Person_Candidate.Person_FName, 'Unspecified') ")
                .Append("AS Candidate, ISNULL(dbo.L_CaseStages.CaseStage_Title, 'Unspecified') AS Case_stage, dbo.L_Products.Product_Name AS Prod_Name, ")
                .Append("ISNULL(dbo.L_ProductTypes.ProductType_Name, '') AS Prod_type, ")
                .Append("CASE WHEN Case_ActiveStatus = 1 THEN 'Active' ELSE 'Inactive' END AS ActiveStatus, dbo.L_Consultant.Con_Name, ")
                .Append("CASE WHEN Case_CompanyID = 0 THEN 'Private Individual' ELSE 'Company' END AS Expr1")
                .Append("FROM         dbo.D_Case INNER JOIN")
                .Append("dbo.L_Consultant ON dbo.D_Case.Case_ConCWID = dbo.L_Consultant.Con_ID LEFT OUTER JOIN")
                .Append("dbo.D_Person AS D_Person_Candidate ON dbo.D_Case.Case_CandidateID = D_Person_Candidate.Person_ID LEFT OUTER JOIN")
                .Append("(SELECT     Event_LinkID, CONVERT(varchar(12), MAX(Event_DTEntered), 103) AS LastEvent, CONVERT(varchar(12), ")
                .Append("MIN(CASE Event_Category WHEN 2 THEN Event_DTScheduled ELSE NULL END), 103) AS NextTask, CONVERT(varchar(12), ")
                .Append("MAX(CASE Event_Category WHEN 3 THEN Event_DTCompleted ELSE NULL END), 103) AS LastComp, COUNT(Event_ID) AS TotalEvents, ")
                .Append("SUM(CASE Event_Category WHEN 2 THEN 1 WHEN 3 THEN 1 ELSE 0 END) AS TotalTasks, ")
                .Append("SUM(CASE Event_Category WHEN 2 THEN 1 ELSE 0 END) AS TotalSched, SUM(CASE Event_Category WHEN 3 THEN 1 ELSE 0 END) ")
                .Append("AS TotalComp, SUM(CASE WHEN Event_Category = 2 AND Event_DTScheduled < getdate() THEN 1 ELSE 0 END) AS TotalOS")
                .Append("FROM dbo.D_Event")
                .Append("WHERE(Event_LinkType = 1)")
                .Append("GROUP BY Event_LinkID) AS D_Event_Case ON dbo.D_Case.Case_ID = D_Event_Case.Event_LinkID LEFT OUTER JOIN")
                .Append("dbo.L_CaseStages ON dbo.D_Case.Case_StatusID = dbo.L_CaseStages.CaseStage_Number LEFT OUTER JOIN")
                .Append("dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
                .Append("(SELECT     EMailAddress_Address, EMailAddress_PersonID")
                .Append("FROM dbo.D_EmailAddress")
                .Append("WHERE      (EMailAddress_Type = 1)) AS D_EMailAddressCandidate_Main ON ")
                .Append("D_Person_Candidate.Person_ID = D_EMailAddressCandidate_Main.EMailAddress_PersonID LEFT OUTER JOIN")
                .Append("dbo.L_Country AS L_Country_CandRes ON D_Person_Candidate.Person_ResidentID = L_Country_CandRes.Country_ID LEFT OUTER JOIN")
                .Append("dbo.L_Products AS L_Products_TypeLink ON dbo.D_Case.Case_ProductID = L_Products_TypeLink.Product_ID LEFT OUTER JOIN")
                .Append("dbo.L_ProductTypes ON L_Products_TypeLink.Product_Type = dbo.L_ProductTypes.ProductType_ID LEFT OUTER JOIN")
                .Append("dbo.L_Country AS L_Country_CandNat ON D_Person_Candidate.Person_NatID = L_Country_CandNat.Country_ID")
                .Append("WHERE     (dbo.D_Case.Case_StatusID < 1000) ")
                .Append(Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConCWID"))
                .Append("ORDER BY dbo.L_Consultant.Con_Name, dbo.D_Case.Case_StatusID, dbo.D_Case.Case_ID, ")
                .Append("CASE WHEN Case_ActiveStatus = 1 THEN 'Active' ELSE 'Inactive' END")
            End If
            
        End With
        Return SQL.ToString
    End Function

    Private Function SQL_OverdueBalance_60()
        Dim SQL As New StringBuilder
        With SQL
            .Append(" SELECT     TOP 100 PERCENT dbo.V_Case.Case_ID, dbo.D_EmailAddress.EMailAddress_Address, dbo.V_Case.Balance, dbo.V_Case.Case_DTSold, ")
            .Append(" dbo.L_Consultant.Con_ID AS CSMId, dbo.L_Consultant.Con_Name AS CSMName, dbo.L_Consultant.Con_Email AS CSMEmail, ")
            .Append(" L_Consultant_1.Con_ID AS ConsulID, L_Consultant_1.Con_Name AS ConsulName, L_Consultant_1.Con_Email AS ConsulEmail, ")
            .Append(" dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name")
            .Append(" FROM         dbo.V_Case INNER JOIN")
            .Append(" dbo.V_Person ON dbo.V_Case.Case_CandidateID = dbo.V_Person.Person_ID INNER JOIN")
            .Append(" dbo.D_EmailAddress ON dbo.V_Person.Person_ID = dbo.D_EmailAddress.EMailAddress_PersonID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConCWID = dbo.L_Consultant.Con_ID INNER JOIN")
            .Append(" dbo.L_Consultant AS L_Consultant_1 ON dbo.V_Case.Case_ConSaleID = L_Consultant_1.Con_ID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
            .Append(" dbo.D_Case ON dbo.V_Case.Case_ID = dbo.D_Case.Case_ID")
            .Append(" WHERE     (dbo.V_Case.Case_CompanyID = 0) AND (dbo.D_EmailAddress.EMailAddress_Type = 1) AND (dbo.V_Case.Balance > 1) AND ")
            .Append(" (dbo.V_Case.Case_ActiveStatus = 0) AND (ISNULL(dbo.D_Case.WriteOff, 0) <> 1) AND (DATEADD(dd, 60, dbo.V_Case.Case_DTSold) < GETDATE())")
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("L_Consultant_1.Con_ID"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("dbo.L_Consultant.Con_ID"))
            End If
            .Append(" ORDER BY dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name")
        End With
        Return SQL.ToString
    End Function
    Private Function SQL_OverdueBalance_90()
        Dim SQL As New StringBuilder
        With SQL
            .Append(" SELECT     TOP 100 PERCENT dbo.V_Case.Case_ID, dbo.D_EmailAddress.EMailAddress_Address, dbo.V_Case.Balance, dbo.V_Case.Case_DTSold, ")
            .Append(" dbo.L_Consultant.Con_ID AS CSMId, dbo.L_Consultant.Con_Name AS CSMName, dbo.L_Consultant.Con_Email AS CSMEmail, ")
            .Append(" L_Consultant_1.Con_ID AS ConsulID, L_Consultant_1.Con_Name AS ConsulName, L_Consultant_1.Con_Email AS ConsulEmail, ")
            .Append(" dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name")
            .Append(" FROM         dbo.V_Case INNER JOIN")
            .Append(" dbo.V_Person ON dbo.V_Case.Case_CandidateID = dbo.V_Person.Person_ID INNER JOIN")
            .Append(" dbo.D_EmailAddress ON dbo.V_Person.Person_ID = dbo.D_EmailAddress.EMailAddress_PersonID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConCWID = dbo.L_Consultant.Con_ID INNER JOIN")
            .Append(" dbo.L_Consultant AS L_Consultant_1 ON dbo.V_Case.Case_ConSaleID = L_Consultant_1.Con_ID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
            .Append(" dbo.D_Case ON dbo.V_Case.Case_ID = dbo.D_Case.Case_ID")
            .Append(" WHERE     (dbo.V_Case.Case_CompanyID = 0) AND (dbo.D_EmailAddress.EMailAddress_Type = 1) AND (dbo.V_Case.Balance > 1) AND ")
            .Append(" (dbo.V_Case.Case_ActiveStatus = 0) AND (ISNULL(dbo.D_Case.WriteOff, 0) <> 1) AND (DATEADD(dd, 90, dbo.V_Case.Case_DTSold) < GETDATE())")
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("L_Consultant_1.Con_ID"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("dbo.L_Consultant.Con_ID"))
            End If
            .Append(" ORDER BY dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name")
        End With
        Return SQL.ToString
    End Function
    Private Function SQL_WebLeadsAssignedToSalespeople(ByVal StartDate As String, ByVal EndDate As String)
        Dim SQL As New StringBuilder
        With SQL

            .Append("Select  A.Case_DtEntered, A.ProductType_Name, IsNull(A.TotalNewLeads,0) as TotalNewLeads, ")
            .Append(" IsNull(B.TotalLeadsAssigned,0) as TotalLeadsAssigned From ( Select  pt.ProductType_Name, ")
            .Append("Convert(DateTime,Convert(varchar(15),c.Case_DtEntered,112))as Case_DtEntered, Count(*) as TotalNewLeads ")
            .Append("From D_Case c Inner Join L_Products p  On c.Case_ProductID = p.Product_ID ")
            .Append(" Inner Join L_ProductTypes pt  On p.Product_Type = pt.ProductType_ID where ")

            .Append(Me.ReportCriteria_Dates("case_dtentered") & " " & "And c.Case_EnteredBy = 'Web' Group By pt.ProductType_Name, ")
            .Append(" Convert(DateTime,Convert(varchar(15),c.Case_DtEntered,112)) ) A ")
            .Append(" Left Outer Join ( Select  pt.ProductType_Name, Convert(DateTime,Convert(varchar(15),c.Case_DtEntered,112))as Case_DtEntered, Count(*) as TotalLeadsAssigned ")
            .Append(" From D_Case c Inner Join L_Products p  On c.Case_ProductID = p.Product_ID Inner Join L_ProductTypes pt  On p.Product_Type = pt.ProductType_ID Where")
            .Append(Me.ReportCriteria_Dates("case_dtentered") & " " & "And c.Case_EnteredBy = 'Web' And c.Case_ConSaleID > 0 And c.Case_ConSaleID <> 159  ")
            .Append("Group By pt.ProductType_Name, Convert(DateTime,Convert(varchar(15),c.Case_DtEntered,112)) ) B ")
            .Append("On A.ProductType_Name = B.ProductType_Name And A.Case_DtEntered = B.Case_DtEntered Order By A.Case_DtEntered,A.ProductType_Name")

            '.Append("Select  A.Case_DtEntered, A.TotalNewLeads,")
            '.Append("B.TotalAssignedLeads From ( Select  Convert(DateTime,Convert(varchar(15),Case_DtEntered,112))as Case_DtEntered, ")
            '.Append("Count(*) as TotalNewLeads From D_Case Where")
            '.Append(Me.ReportCriteria_Dates("case_dtentered") & " " & "And Case_EnteredBy = 'Web'")
            '.Append(" Group By Convert(DateTime,Convert(varchar(15),Case_DtEntered,112)) ) A")
            '.Append("  Left Outer Join ( Select  Convert(DateTime,Convert(varchar(15),Case_DtEntered,112))as Case_DtEntered,")
            '.Append(" Count(*) as TotalAssignedLeads From D_Case Where  " & Me.ReportCriteria_Dates("case_dtentered") & " And Case_EnteredBy = 'Web' ")
            '.Append(" And Case_ConSaleID > 0 And Case_ConSaleID <> 159")
            '.Append(" Group By Convert(DateTime,Convert(varchar(15),Case_DtEntered,112)) ) B")
            '.Append(" On A.Case_DtEntered = B.Case_DtEntered Order By A.Case_DtEntered")
        End With
        Return SQL.ToString
    End Function
    Private Function SQL_WebSalesPerDay(ByVal StartDate As String, ByVal EndDate As String)

        Dim SQL As New StringBuilder

        With SQL
            .Append("sp_WebSalesByCountry '" & StartDate & "','" & EndDate & "'")
        End With

        Return SQL.ToString

    End Function

    Private Function SQL_WebLeadsPerDay(ByVal StartDate As String, ByVal EndDate As String)

        Dim SQL As New StringBuilder

        With SQL
            .Append(" SELECT     TOP 100 PERCENT dbo.D_Case.Case_ID AS CaseID, dbo.D_Case.Case_DtEntered AS DTEntered, ")
            .Append(" dbo.L_ProductTypes.ProductType_Name AS ProdType, dbo.L_Products.Product_Name AS ProdName")
            .Append(" FROM         dbo.L_ProductTypes INNER JOIN")
            .Append(" dbo.L_Products ON dbo.L_ProductTypes.ProductType_ID = dbo.L_Products.Product_Type INNER JOIN")
            .Append(" dbo.D_Case ON dbo.L_Products.Product_ID = dbo.D_Case.Case_ProductID where ")

            .Append(Me.ReportCriteria_Dates("case_dtentered") & " ")
            .Append(" AND (dbo.D_Case.Case_EnteredBy = 'Web') ORDER BY dbo.L_ProductTypes.ProductType_Name, dbo.D_Case.Case_DtEntered")
        End With

        Return SQL.ToString

    End Function
    Private Function SQL_CSMCaseStage(ByVal StartDate As String, ByVal EndDate As String)

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT     TOP 100 PERCENT dbo.V_Case.Case_ID, dbo.V_Case.CaseStage_Title, dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name, ")
            .Append(" dbo.L_Consultant.Con_Name")
            .Append(" FROM         dbo.V_Case INNER JOIN")
            .Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConCWID = dbo.L_Consultant.Con_ID")
            .Append(" WHERE     (dbo.V_Case.Case_StatusID BETWEEN 100 AND 999)  ") 'AND (dbo.V_Case.Case_ConCWID = 269)")
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("case_consaleid", False))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("case_concwid", False))
            End If
            .Append(" ORDER BY dbo.V_Case.Case_ID")

            '.Append("SELECT A_Invoices.Invoice_ID, MAX(A_Invoices.Invoice_Date) AS Inv_Date, MAX(A_Invoices.Invoice_DateDue) AS DateDue, MAX(A_Invoices.Invoice_CaseID) ")
            '.Append("AS CaseID, SUM(A_Charges.Charge_Amount) AS Net, SUM(A_Charges.Charge_Amount - A_Charges.Charge_TPCost) AS Income, CAST(SUM(A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            '.Append("CAST(SUM(A_Charges.Charge_Amount + A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            '.Append("MAX(D_Person.Person_FName + ' ' + D_Person.Person_LName) AS FullName, MAX(L_Products.Product_Name) AS Product, ")
            '.Append("MAX(ISNULL(D_Company.Company_Name, 'Private Individuals')) AS CompanyName, MAX(ISNULL(D_Company.Company_ID, '')) AS CompanyID, ")
            '.Append("Max(L_Consultant.Con_Name) as SaleCon ")
            '.Append("from A_Invoices INNER JOIN ")
            '.Append("A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID LEFT OUTER JOIN ")
            '.Append("D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID LEFT OUTER JOIN ")
            '.Append("L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN ")
            '.Append("D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID LEFT OUTER JOIN ")
            '.Append("L_Consultant ON D_Case.Case_ConSaleID = L_Consultant.Con_ID LEFT OUTER JOIN ")
            '.Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID ")
            '.Append("WHERE ") ' (Invoice_Date BETWEEN '06/03/2006 23:59:59' AND '06/10/2006 23:59:59') 
            'dats = Me.ReportCriteria_Dates("invoice_date")
            '.Append(dats)
            'If dats = "" Then
            '    If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '        .Append(Me.ReportCriteria_Consultant("case_consaleid", True))
            '    ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '        .Append(Me.ReportCriteria_Consultant("case_concwid", True))
            '    End If
            'Else
            '    If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '        .Append(Me.ReportCriteria_Consultant("case_consaleid", False))
            '    ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '        .Append(Me.ReportCriteria_Consultant("case_concwid"))
            '    End If
            'End If

            ''.Append(Me.ReportCriteria_Consultant("case_consaleid"))
            'If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '    .Append(Me.ReportCriteria_Consultant("case_consaleid", False))
            'ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '    .Append(Me.ReportCriteria_Consultant("case_concwid"))
            'End If

            '.Append(Me.ReportCriteria_Product("case_productid"))
            '.Append(Me.ReportCriteria_SLA_Accounts)
            '.Append(Me.ReportCriteria_Client_Types)

            '.Append(" GROUP BY A_Invoices.Invoice_ID ORDER BY A_Invoices.Invoice_ID")
        End With


        Return Sql.ToString
    End Function
    Private Function SQL_Refunds(ByVal StartDate As String, ByVal EndDate As String)

        Dim SQL As New StringBuilder

        With SQL
            .Append(" SELECT     Payment_Type AS type, Payment_ID AS id, Payment_CaseID AS caseid, Payment_DTEntered AS dtentered, Payment_EnteredBy AS payBy, ")
            .Append(" Payment_Method AS method, Payment_Amount AS amt")
            .Append(" FROM dbo.A_Payments")
            .Append(" WHERE     (Payment_Type LIKE 'refund%' or  Payment_Type LIKE 'Charge Back') and ")

            .Append(Me.ReportCriteria_Dates("Payment_DTEntered") & " ")

            .Append(" ORDER BY Payment_DTEntered")
        End With

        Return SQL.ToString

    End Function

    Private Function GenerateConversionProdReport(ByVal LeadsSQL As String, ByVal SalesSQL As String) As DataTable

        Dim myLeads As DataTable
        Dim mySales As DataTable
        Dim myRow, myRow2 As DataRow

        myLeads = Data.GetQueryResults(LeadsSQL)
        mySales = Data.GetQueryResults(SalesSQL)

        'myLeads.Columns.Add(New DataColumn("TotalSales", System.Type.GetType("System.Int16")))

        For Each myRow In mySales.Rows

            'Find consultant in leads and copy sales figure into new column
            For Each myRow2 In myLeads.Rows
                If myRow.Item("product_name") = myRow2.Item("product_name") And myRow.Item("branch_name") = myRow2.Item("branch_name") Then
                    myRow2.Item("totalsales") = myRow("totalsales")
                    myRow2.Item("convrate") = (100 / myRow2.Item("totalleads")) * myRow("totalsales")

                    Exit For
                End If
            Next


        Next

        Return myLeads


    End Function

    Private Shared Function ManipulateCollec(ByVal caseids As DataTable) As String
        Dim strCase As String
        Dim i As Integer
        For i = 0 To caseids.Rows.Count - 1
            If strCase = "" Then
                strCase = caseids.Rows(i).Item(0).ToString
            Else
                strCase = strCase & "," & caseids.Rows(i).Item(0).ToString
            End If
        Next
        Return strCase
    End Function


    Private Shared Function GenerateWriteOffReportByConsul(ByVal SQL As String) As DataTable

        Dim strSQL As StringBuilder
        Dim dtTemp, dtData1, myData As DataTable
        Dim dr() As DataRow
        Dim i As Integer
        myData = Data.GetQueryResults(SQL)
        dtTemp = New DataTable
        dtTemp.Columns.Add("Invoice_ID")
        If Not myData Is Nothing Then
            If myData.Rows.Count > 0 Then
                For i = 0 To myData.Rows.Count - 1
                    dtTemp.Rows.Add(myData.Rows(i).Item("Invoice_ID"))
                Next
            End If
        End If
        Dim strInv As String = ManipulateCollec(dtTemp)
        strSQL = New StringBuilder
        With strSQL
            .Append("SELECT     TOP 100 PERCENT Charge_InvoiceID FROM  dbo.A_Charges WHERE  (Charge_InvoiceID IN (")
            .Append(strInv)
            .Append(")) AND (Charge_Type <> 'Writeoff')")
        End With

        dtData1 = Data.GetQueryResults(strSQL.ToString)
        Dim strId As String = ManipulateCollec(dtData1)
        If Not strId Is Nothing Then
            dr = myData.Select("Invoice_id in (" & strId & ")")
        End If
        'If Not dtData1 Is Nothing Then
        '    If dtData1.Rows.Count > 0 Then
        '        For i = 0 To dtData1.Rows.Count - 1
        '            dr = myData.Select("Invoice_ID = " & dtData1.Rows(i).Item(0).ToString)

        '        Next
        '    End If
        'End If
        If Not dr Is Nothing Then
            For i = 0 To dr.Length - 1
                myData.Rows.Remove(dr(i))
            Next
        End If
        Return myData
    End Function

    Private Shared Function GenerateInvoiceReportByConsul_old(ByVal SQL As String) As DataTable

        Dim strSQL As StringBuilder
        Dim strInv As String = ""
        Dim dtTemp, dtData1, myData As DataTable
        Dim dr() As DataRow
        Dim i As Integer
        myData = Data.GetQueryResults(SQL)
        dtTemp = New DataTable
        dtTemp.Columns.Add("Invoice_ID")
        If Not myData Is Nothing Then
            If myData.Rows.Count > 0 Then
                For i = 0 To myData.Rows.Count - 1
                    dtTemp.Rows.Add(myData.Rows(i).Item("Invoice_ID"))
                Next
            End If
        End If
        If dtTemp.Rows.Count > 0 Then
            strInv = ManipulateCollec(dtTemp)
        End If
        If strInv <> "" Then


            strSQL = New StringBuilder
            With strSQL
                .Append("SELECT     TOP 100 PERCENT Charge_InvoiceID FROM  dbo.A_Charges WHERE  (Charge_InvoiceID IN (")
                .Append(strInv)
                .Append(")) AND (Charge_Type LIKE 'Writeoff')")
            End With

            dtData1 = Data.GetQueryResults(strSQL.ToString)
            Dim strId As String = ManipulateCollec(dtData1)
            If Not strId Is Nothing Then
                dr = myData.Select("Invoice_id in (" & strId & ")")
            End If
            If Not dr Is Nothing Then

                For i = 0 To dr.Length - 1
                    myData.Rows.Remove(dr(i))
                Next
            End If
        End If
        Return myData
    End Function
    Private Shared Function GenerateInvoiceReportByConsul(ByVal SQL As String, ByVal SQL1 As String) As DataTable

        Dim strSQL As StringBuilder
        Dim strInv As String = ""
        Dim dtTemp, dtData1, myData, mydata1 As DataTable
        Dim dr() As DataRow
        Dim i As Integer
        myData = Data.GetQueryResults(SQL)
        mydata1 = Data.GetQueryResults(SQL1)
        myData.Merge(mydata1)
        dtTemp = New DataTable
        dtTemp.Columns.Add("Invoice_ID")

        If Not myData Is Nothing Then
            If myData.Rows.Count > 0 Then
                For i = 0 To myData.Rows.Count - 1
                    dtTemp.Rows.Add(myData.Rows(i).Item("Invoice_ID"))
                Next
            End If
        End If
        If dtTemp.Rows.Count > 0 Then
            strInv = ManipulateCollec(dtTemp)
        End If
        If strInv <> "" Then


            strSQL = New StringBuilder
            With strSQL
                .Append("SELECT     TOP 100 PERCENT Charge_InvoiceID FROM  dbo.A_Charges WHERE  (Charge_InvoiceID IN (")
                .Append(strInv)
                .Append(")) AND (Charge_Type LIKE 'Writeoff')")
            End With

            dtData1 = Data.GetQueryResults(strSQL.ToString)
            Dim strId As String = ManipulateCollec(dtData1)
            If Not strId Is Nothing Then
                dr = myData.Select("Invoice_id in (" & strId & ")")
            End If
            If Not dr Is Nothing Then

                For i = 0 To dr.Length - 1
                    myData.Rows.Remove(dr(i))
                Next
            End If
        End If
        Return myData
    End Function
    Private Shared Function GenerateConversionConReport(ByVal LeadsSQL As String, ByVal SalesSQL As String) As DataTable

        Dim myLeads As DataTable
        Dim mySales As DataTable
        Dim myRow, myRow2 As DataRow

        myLeads = Data.GetQueryResults(LeadsSQL)
        mySales = Data.GetQueryResults(SalesSQL)

        If myLeads.Rows.Count = 0 Then
            LeadsSQL = LeadsSQL.Replace("c.Case_DTAllocated", "c.Case_DTEntered")
            SalesSQL = SalesSQL.Replace("c.Case_DTAllocated", "c.Case_DTEntered")
            myLeads = Data.GetQueryResults(LeadsSQL)
            mySales = Data.GetQueryResults(SalesSQL)
        End If
        'myLeads.Columns.Add(New DataColumn("TotalSales", System.Type.GetType("System.Int16")))

        For Each myRow In mySales.Rows

            'Find consultant in leads and copy sales figure into new column
            For Each myRow2 In myLeads.Rows
                If myRow.Item("con_name") = myRow2.Item("con_name") Then ' And myRow.Item("department_name") = myRow2.Item("department_name") Then
                    myRow2.Item("totalsales") = myRow("totalsales")
                    myRow2.Item("convrate") = (100 / myRow2.Item("totalleads")) * myRow("totalsales")

                    Exit For
                End If
            Next


        Next

        Return myLeads



    End Function
    Private Function SQL_ConversionByProduct() As String()

        Dim SQL As New StringBuilder
        Dim arrSQL(1) As String

        'Leads
        With SQL


            .Append("SELECT     TOP 100 PERCENT p.Product_Name, co.Country_Name, COUNT(*) AS totalleads, 0 AS totalsales, 0.0 AS convrate, dbo.L_Branch.Branch_Name, ")
            .Append(" pt.ProductType_Name")
            .Append(" FROM  dbo.D_Case AS c INNER JOIN")
            .Append(" dbo.L_Products AS p ON c.Case_ProductID = p.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes AS pt ON p.Product_Type = pt.ProductType_ID INNER JOIN")
            .Append(" dbo.L_Country AS co ON p.Product_DestinationID = co.Country_ID INNER JOIN")
            .Append(" dbo.L_Branch ON pt.ProductType_BranchID = dbo.L_Branch.Branch_ID")
            .Append(" where")
            .Append(ReportCriteria_Dates("c.Case_DTEntered"))
            .Append(" GROUP BY p.Product_Name, co.Country_Name, dbo.L_Branch.Branch_Name, pt.ProductType_Name ")
            .Append(" order by p.product_name")

            arrSQL(0) = .ToString()
        End With

        SQL = New StringBuilder

        'Sales
        With SQL
            .Append("SELECT  TOP 100 PERCENT p.Product_Name, co.Country_Name, COUNT(*) AS totalsales, 0.0 AS conrate, dbo.L_Branch.Branch_Name,   pt.ProductType_Name ")
            .Append(" FROM  dbo.D_Case AS c INNER JOIN")
            .Append(" dbo.L_Products AS p ON c.Case_ProductID = p.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes AS pt ON p.Product_Type = pt.ProductType_ID INNER JOIN")
            .Append(" dbo.L_Country AS co ON p.Product_DestinationID = co.Country_ID INNER JOIN")
            .Append(" dbo.L_Branch ON pt.ProductType_BranchID = dbo.L_Branch.Branch_ID")
            .Append(" where")
            .Append(ReportCriteria_Dates("c.Case_DTEntered"))
            .Append("  AND (c.Case_DTSold IS NOT NULL) AND (c.Case_StatusID <> 1030)")
            .Append(" GROUP BY p.Product_Name, co.Country_Name, dbo.L_Branch.Branch_Name, pt.ProductType_Name ")
            .Append(" order by p.product_name")
            arrSQL(1) = .ToString
        End With

        Return arrSQL

    End Function
    Private Function SQL_ConversionByConsultant() As String()

        Dim SQL As New StringBuilder
        Dim arrSQL(1) As String

        'Leads

        With SQL
            .Append("select con.con_name,count(*) as totalleads, 0 as totalsales, 0.0 as convrate ")
            .Append("from d_case c, l_products p, l_consultant con ")
            .Append("where ")
            .Append(ReportCriteria_Dates("c.Case_DTEntered"))
            .Append(" and c.case_productid = p.product_id ")
            .Append("and c.case_consaleid = con.con_id ")
            .Append("group by con.con_name ")
            .Append("order by con.con_name")
            arrSQL(0) = .ToString
        End With

        'Sales

        SQL = New StringBuilder

        With SQL

            .Append("select con.con_name,count(*) as totalsales ")
            .Append("from d_case c, l_products p, l_consultant con ")
            .Append("where ")
            .Append(ReportCriteria_Dates("c.Case_DTsold"))
            .Append(" and c.case_dtsold is not null and  c.case_dtsold <> '1900-01-01 00:00:00.000' and c.case_statusid <> 1030")
            .Append(" and c.case_productid = p.product_id")
            .Append(" and c.case_consaleid = con.con_id")
            .Append(" group by con.con_name")
            .Append(" order by con.con_name")
            arrSQL(1) = .ToString
        End With

        Return arrSQL

    End Function
    Private Function SQL_MigrationCaseStages() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select c1.case_id as caseid, s.casestage_title as stage, per.person_fname + ' ' + per.person_lname as client,")
            .Append("con1.con_name as salescon,coalesce(con2.con_name,'UNASSIGNED') as caseworker,p.product_name,")
            .Append("c1.case_dtsold,sum(ch.charge_amount) as charged, sum(pay.payment_amount) as received ")
            .Append("from d_case c1, l_products p, l_producttypes pt, l_casestages s, d_person per, l_consultant con1, a_charges ch, a_payments pay,")
            .Append("d_case c2 left join l_consultant con2 on con2.con_id = c2.case_concwid  WHERE c1.case_productid = p.product_id ")
            .Append("and p.product_type = pt.producttype_id and pt.producttype_destinationid <> 71 ")
            .Append("and c1.case_statusid between 100 and 999 and c1.case_statusid = s.casestage_number ")
            .Append("and c1.case_dtentered >= getdate() -365 ")
            .Append("and c1.case_candidateid = per.person_id ")
            .Append("and c1.case_consaleid = con1.con_id and c1.case_id = c2.case_id ")
            .Append("and c1.case_id = ch.charge_caseid ")
            .Append("and c1.case_id = pay.payment_caseid group by c1.case_id, s.casestage_title, per.person_fname + ' ' + per.person_lname,con1.con_name,coalesce(con2.con_name,'UNASSIGNED'),p.product_name, ")
            .Append("c1.case_dtsold order by c1.case_dtsold")
        End With

        Return SQL.ToString

    End Function

    Private Function SQL_EnteredBySummary() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT Sum(CASE WHEN D_Case.Case_StatusID between 0 AND 99 THEN 1 ")
            .Append("WHEN  D_Case.Case_StatusID between 100 AND 999 THEN 0 END)  AS lead, ")
            .Append("Sum(CASE WHEN D_Case.Case_StatusID between 0 AND 99 THEN 0 ")
            .Append("WHEN  D_Case.Case_StatusID between 100 AND 999 THEN 1 END)  AS sold,")
            .Append("Sum(CASE WHEN D_Case.Case_StatusID >1000 THEN 1 ")
            .Append("When D_Case.Case_StatusID <1000  then 0 END) AS Cancelled,")
            .Append("L_Consultant_1.Con_Name AS Sale, D_Case.Case_EnteredBy AS [Added By] ")
            .Append("FROM D_Case LEFT OUTER JOIN ")
            .Append("L_Consultant L_Consultant_1 ON D_Case.Case_ConSaleID = L_Consultant_1.Con_ID LEFT OUTER JOIN ")
            .Append("L_Consultant ON D_Case.Case_ConCWID = L_Consultant.Con_ID ")
            .Append("WHERE NOT (D_Case.Case_CompanyID = 3065) AND ")
            .Append(Me.ReportCriteria_Dates("case_dtentered") & " ")
            .Append("GROUP BY L_Consultant_1.Con_Name, D_Case.Case_EnteredBy ")
            .Append("ORDER BY L_Consultant_1.Con_Name")
        End With

        Return SQL.ToString


    End Function

    Private Function SQL_OpenCasesNoInvoice() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Case.Case_ID, ISNULL(L_Products.Product_Name, 'unspecified') AS Product, L_CaseStages.CaseStage_Title, D_Case.Case_DTStatus,")
            .Append("D_Case.Case_DTSold, ISNULL(L_Consultant_Sales.Con_Name, 'Unspecified') AS SalesCon, ISNULL(L_Consultant_CW.Con_Name, 'Unspecified') ")
            .Append("AS CW, ISNULL(D_Company.Company_Name, 'Private Individual') AS Company, V_Case.GrossQuote, V_Case.IncomeQuote ")
            .Append("FROM L_Consultant L_Consultant_CW RIGHT OUTER JOIN ")
            .Append("V_Case RIGHT OUTER JOIN ")
            .Append("D_Case ON V_Case.Case_ID = D_Case.Case_ID LEFT OUTER JOIN ")
            .Append("A_Invoices ON D_Case.Case_ID = A_Invoices.Invoice_CaseID LEFT OUTER JOIN ")
            .Append("L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN ")
            .Append("L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number ON ")
            .Append("L_Consultant_CW.Con_ID = D_Case.Case_ConCWID LEFT OUTER JOIN ")
            .Append("L_Consultant L_Consultant_Sales ON D_Case.Case_ConSaleID = L_Consultant_Sales.Con_ID LEFT OUTER JOIN ")
            .Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID  WHERE (D_Case.Case_StatusId Between 100 and 990) AND (Invoice_ID is null) ")
            .Append(Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConSaleID"))
            .Append(" and " & Me.ReportCriteria_Dates("d_case.Case_DTStatus"))
            .Append(Me.ReportCriteria_Product("case_productid") & " ")
            .Append("Order By D_Case.Case_ID")

        End With

        Return SQL.ToString

    End Function

    Private Function SQL_SalesStatusSummary() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT MAX(L_CaseStages.CaseStage_Title) AS Stage, COUNT(D_Case.Case_ID) AS NoCases, AVG(DATEDIFF(day, D_Case.Case_DTStatus, GETDATE())) ")
            .Append("AS AvgTime, SUM(CASE D_Case.Case_CompanyID WHEN 0 THEN 1 ELSE 0 END) AS NoPrivate ")
            .Append("FROM D_Case LEFT OUTER JOIN ")
            .Append("L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number  WHERE  (D_Case.Case_StatusID < 100) ")
            .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            .Append(Me.ReportCriteria_Product("case_productid"))
            .Append("GROUP BY D_Case.Case_StatusID ")
        End With

        Return SQL.ToString

    End Function

    Private Function SQL_PipeLinePreSale() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Case.Case_ID, V_CaseCharges.GrossQuote, V_CaseCharges.IncomeQuote, isnull(Product_Name,'Unspecified') as Product, L_Consultant.Con_Name, ISNULL(D_Company.Company_Name, 'Private Individuals') ")
            .Append("AS CoName, L_CaseStages.CaseStage_Title, D_Case.Case_DTStatus, ")
            .Append("ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Contact, ")
            .Append("ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Candidate ")
            .Append("FROM D_Person D_Person_Contact RIGHT OUTER JOIN ")
            .Append("L_CaseStages RIGHT OUTER JOIN ")
            .Append("L_Products RIGHT OUTER JOIN ")
            .Append("D_Case INNER JOIN ")
            .Append("V_CaseCharges ON D_Case.Case_ID = V_CaseCharges.Case_ID INNER JOIN ")
            .Append("L_Consultant ON D_Case.Case_ConSaleID = L_Consultant.Con_ID ON L_Products.Product_ID = D_Case.Case_ProductID ON ")
            .Append("L_CaseStages.CaseStage_Number = D_Case.Case_StatusID LEFT OUTER JOIN ")
            .Append("D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID ON ")
            .Append("D_Person_Contact.Person_ID = D_Case.Case_ContactID LEFT OUTER JOIN ")
            .Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID  WHERE (D_Case.Case_StatusID < 100) ")
            .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            .Append(" and " & Me.ReportCriteria_Dates("Case_DTStatus"))
            .Append(Me.ReportCriteria_Product("case_productid"))

            '(Case_DTStatus BETWEEN '07/31/2006 23:59:59' AND '08/31/2006 23:59:59')")
        End With

        Return SQL.ToString

    End Function
    Private Function SQL_SalePipeline() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Case.Case_ID, V_CaseCharges.GrossQuote, V_CaseCharges.IncomeQuote, isnull(Product_Name,'Unspecified') as Product, L_Consultant.Con_Name, ISNULL(D_Company.Company_Name, 'Private Individuals') ")
            .Append("AS CoName, L_CaseStages.CaseStage_Title, D_Case.Case_DTStatus, ")
            .Append("ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Contact, ")
            .Append("ISNULL(D_Person_Contact.Person_FName + ' ' + D_Person_Contact.Person_LName, '') AS Candidate ")
            .Append("FROM D_Person D_Person_Contact RIGHT OUTER JOIN ")
            .Append("L_CaseStages RIGHT OUTER JOIN ")
            .Append("L_Products RIGHT OUTER JOIN ")
            .Append("D_Case INNER JOIN ")
            .Append("V_CaseCharges ON D_Case.Case_ID = V_CaseCharges.Case_ID INNER JOIN ")
            .Append("L_Consultant ON D_Case.Case_ConSaleID = L_Consultant.Con_ID ON L_Products.Product_ID = D_Case.Case_ProductID ON ")
            .Append("L_CaseStages.CaseStage_Number = D_Case.Case_StatusID LEFT OUTER JOIN ")
            .Append("D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID ON ")
            .Append("D_Person_Contact.Person_ID = D_Case.Case_ContactID LEFT OUTER JOIN ")
            .Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID  WHERE (D_Case.Case_StatusID < 100) ")
            .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            '.Append(" and " & Me.ReportCriteria_Dates("Case_DTStatus"))
            .Append(Me.ReportCriteria_Product("case_productid"))

            '(Case_DTStatus BETWEEN '07/31/2006 23:59:59' AND '08/31/2006 23:59:59')")
        End With

        Return SQL.ToString

    End Function


    Private Function SQL_VisasExpiring() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Person.Person_ID AS PersonID,")
            .Append("D_Person.Person_Title + ' ' + D_Person.Person_Fname + ' ' + D_Person.Person_Mname + ' ' + D_Person.Person_Lname AS PersonName, ")
            .Append("L_Country.Country_Nationality AS Nationality, ISNULL(D_Company.Company_Name, 'none') AS CompanYName, ")
            .Append("L_UKVisa.UKVisa_Name AS CurrentVisa, D_Person.Person_ExpiryDT AS VisaExpiry, ISNULL(L_Consultant_1.Con_Name, 'none') AS AccountManager, ")
            .Append("ISNULL(L_Consultant_1.Con_Name, 'none') AS PersonalConsultant, D_company.company_ID as CoID ")
            .Append("FROM L_Consultant L_Consultant_1 RIGHT OUTER JOIN ")
            .Append("D_Person INNER JOIN ")
            .Append("L_Country ON D_Person.Person_NatID = L_Country.Country_ID ON L_Consultant_1.Con_ID = D_Person.Person_AccountManager LEFT OUTER JOIN ")
            .Append("L_Consultant L_Consultant_2 RIGHT OUTER JOIN ")
            .Append("D_Company ON L_Consultant_2.Con_ID = D_Company.Company_AccountManagerID ON ")
            .Append("D_Person.Person_CompanyID = D_Company.Company_ID LEFT OUTER JOIN ")
            .Append("L_UKVisa ON D_Person.Person_UKVisaID = L_UKVisa.UKVisa_ID  ")
            .Append("WHERE " & Me.ReportCriteria_Dates("person_expirydt")) '(D_Person.Person_ExpiryDT BETWEEN '07/31/2006 23:59:59' AND '08/31/2006 23:59:59') ORDER BY D_Person.Person_ExpiryDT")

        End With

        Return SQL.ToString


    End Function
    Private Function SQL_PaymentDue(ByVal sDays As Integer) As String
        Dim SQL As New StringBuilder
        With SQL
            .Append(" SELECT     TOP 100 PERCENT dbo.L_Country.Country_Name, dbo.V_Case.Case_ID, dbo.V_Case.Balance, dbo.V_Case.Case_DTSold, ")
            .Append(" dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name, dbo.V_Case.LastPaidDate")
            .Append(" FROM         dbo.V_Case INNER JOIN")
            .Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
            .Append(" dbo.D_Case ON dbo.V_Case.Case_ID = dbo.D_Case.Case_ID INNER JOIN")
            .Append(" dbo.L_Country ON dbo.L_ProductTypes.ProductType_DestinationID = dbo.L_Country.Country_ID ")
            '.Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID")
            .Append(" WHERE     (dbo.V_Case.Case_CompanyID = 0) ")
            .Append("  AND (dbo.V_Case.Balance > 1) AND ")
            .Append(" (dbo.V_Case.Case_ActiveStatus = 0) AND (ISNULL(dbo.D_Case.WriteOff, 0) <> 1) ")
            Select Case sDays
                Case 30
                    .Append("AND (DATEADD(dd, 30, dbo.V_Case.Case_DTSold) < GETDATE())")
                Case 60
                    .Append("AND (DATEADD(dd, 60, dbo.V_Case.Case_DTSold) < GETDATE())")
                Case 90
                    .Append("AND (DATEADD(dd, 90, dbo.V_Case.Case_DTSold) < GETDATE())")
            End Select
            .Append(Me.ReportCriteria_Consultant("dbo.V_Case.Case_ConSaleID"))
            .Append(" ORDER BY dbo.L_Country.Country_Name")
        End With
        Return SQL.ToString
    End Function
    Private Function SQL_PaymentDueSLA(ByVal sDays As Integer) As String
        Dim SQL As New StringBuilder
        With SQL

            .Append(" SELECT     TOP 100 PERCENT dbo.L_Country.Country_Name, dbo.V_Case.Case_ID, dbo.V_Case.Balance, dbo.V_Case.Case_DTSold, ")
            .Append(" dbo.L_Consultant.Con_Name AS CSMName, dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name, dbo.V_Case.LastPaidDate, ")
            .Append(" dbo.D_Company.Company_Name")
            .Append(" FROM dbo.V_Case INNER JOIN")
            .Append(" dbo.V_Person ON dbo.V_Case.Case_CandidateID = dbo.V_Person.Person_ID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConCWID = dbo.L_Consultant.Con_ID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
            .Append(" dbo.D_Case ON dbo.V_Case.Case_ID = dbo.D_Case.Case_ID INNER JOIN")
            .Append(" dbo.L_Country ON dbo.L_ProductTypes.ProductType_DestinationID = dbo.L_Country.Country_ID INNER JOIN")
            .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID")
            .Append(" WHERE     (dbo.V_Case.Case_CompanyID > 0) AND (dbo.V_Case.Balance > 1) AND (dbo.V_Case.Case_ActiveStatus = 0) AND (ISNULL(dbo.D_Case.WriteOff, 0) <> 1)  ")
            Select Case sDays
                Case 30
                    .Append("AND (DATEADD(dd, 30, dbo.V_Case.Case_DTSold) < GETDATE())")
                Case 60
                    .Append("AND (DATEADD(dd, 60, dbo.V_Case.Case_DTSold) < GETDATE())")
                Case 90
                    .Append("AND (DATEADD(dd, 90, dbo.V_Case.Case_DTSold) < GETDATE())")
            End Select
            .Append(Me.ReportCriteria_Consultant("dbo.V_Case.Case_ConSaleID"))
            .Append(" ORDER BY dbo.L_Country.Country_Name")

            '.Append(" SELECT     TOP 100 PERCENT dbo.L_Country.Country_Name, dbo.V_Case.Case_ID, dbo.D_EmailAddress.EMailAddress_Address AS Email, dbo.V_Case.Balance, ")
            '.Append(" dbo.V_Case.Case_DTSold, dbo.L_Consultant.Con_Name AS CSMName, L_Consultant_1.Con_Name AS ConsulName, ")
            '.Append(" dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name, dbo.V_Case.LastPaidDate, dbo.D_Company.Company_Name")
            '.Append(" FROM         dbo.V_Case INNER JOIN")
            '.Append(" dbo.V_Person ON dbo.V_Case.Case_CandidateID = dbo.V_Person.Person_ID INNER JOIN")
            '.Append(" dbo.D_EmailAddress ON dbo.V_Person.Person_ID = dbo.D_EmailAddress.EMailAddress_PersonID INNER JOIN")
            '.Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConCWID = dbo.L_Consultant.Con_ID INNER JOIN")
            '.Append(" dbo.L_Consultant AS L_Consultant_1 ON dbo.V_Case.Case_ConSaleID = L_Consultant_1.Con_ID INNER JOIN")
            '.Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            '.Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
            '.Append(" dbo.D_Case ON dbo.V_Case.Case_ID = dbo.D_Case.Case_ID INNER JOIN")
            '.Append(" dbo.L_Country ON dbo.L_ProductTypes.ProductType_DestinationID = dbo.L_Country.Country_ID INNER JOIN")
            '.Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID")
            '.Append(" WHERE     (dbo.D_EmailAddress.EMailAddress_Type = 1) AND (dbo.V_Case.Case_CompanyID > 0) AND (dbo.V_Case.Balance > 1) AND ")
            '.Append(" (dbo.V_Case.Case_ActiveStatus = 0) AND (ISNULL(dbo.D_Case.WriteOff, 0) <> 1) ")
            'Select Case sDays
            '    Case 30
            '        .Append("AND (DATEADD(dd, 30, dbo.V_Case.Case_DTSold) < GETDATE())")
            '    Case 60
            '        .Append("AND (DATEADD(dd, 60, dbo.V_Case.Case_DTSold) < GETDATE())")
            '    Case 90
            '        .Append("AND (DATEADD(dd, 90, dbo.V_Case.Case_DTSold) < GETDATE())")
            'End Select
            '.Append(Me.ReportCriteria_Consultant("dbo.V_Case.Case_ConSaleID"))
            '.Append(" ORDER BY dbo.L_Country.Country_Name")
        End With
        Return SQL.ToString
    End Function

    Private Function SQL_LeadsByProduct() As String

        Dim SQL As New StringBuilder

        With SQL
            '.Append(" SELECT     TOP 100 PERCENT A.ProductType_Name, A.Product_Name, ISNULL(A.TotalNewLeads, 0) AS TotalNewLeads")
            '.Append(" FROM         (SELECT     pt.ProductType_Name, p.Product_Name, CONVERT(DateTime, CONVERT(varchar(15), c.Case_DtEntered, 112)) AS Case_DtEntered, COUNT(*) ")
            '.Append(" AS TotalNewLeads")
            '.Append(" FROM          dbo.D_Case AS c INNER JOIN")
            '.Append(" dbo.L_Products AS p ON c.Case_ProductID = p.Product_ID INNER JOIN")
            '.Append(" dbo.L_ProductTypes AS pt ON p.Product_Type = pt.ProductType_ID")
            '.Append(" WHERE      ( " & Me.ReportCriteria_Dates("Case_DtEntered") & ") ")
            '.Append(" GROUP BY pt.ProductType_Name, p.Product_Name, CONVERT(DateTime, CONVERT(varchar(15), c.Case_DtEntered, 112))) AS A LEFT OUTER JOIN")
            '.Append(" (SELECT     pt.ProductType_Name, p.Product_Name, CONVERT(DateTime, CONVERT(varchar(15), c.Case_DtEntered, 112)) AS Case_DtEntered, ")
            '.Append(" COUNT(*) AS TotalLeadsAssigned")
            '.Append(" FROM          dbo.D_Case AS c INNER JOIN")
            '.Append(" dbo.L_Products AS p ON c.Case_ProductID = p.Product_ID INNER JOIN")
            '.Append(" dbo.L_ProductTypes AS pt ON p.Product_Type = pt.ProductType_ID")
            '.Append(" WHERE      ( " & Me.ReportCriteria_Dates("Case_DtEntered") & ") AND ")
            '.Append(" (c.Case_ConSaleID > 0) AND (c.Case_ConSaleID <> 159)")
            '.Append(" GROUP BY pt.ProductType_Name, p.Product_Name, CONVERT(DateTime, CONVERT(varchar(15), c.Case_DtEntered, 112))) AS B ON ")
            '.Append(" A.ProductType_Name = B.ProductType_Name AND A.Product_Name = B.Product_Name AND A.Case_DtEntered = B.Case_DtEntered")
            '.Append(" ORDER BY A.Case_DtEntered, A.ProductType_Name")




            .Append(" SELECT     TOP 100 PERCENT dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name, COUNT(*) AS TotalNewLeads")
            .Append(" FROM         dbo.D_Case INNER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID")
            .Append(" WHERE      ( " & Me.ReportCriteria_Dates("Case_DtEntered") & ") ")
            .Append(" GROUP BY dbo.L_Products.Product_Name, dbo.L_ProductTypes.ProductType_Name")
            .Append(" ORDER BY dbo.L_ProductTypes.ProductType_Name")
        End With

        Return SQL.ToString


    End Function
    Private Function SQL_CaseStages() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT L_StageDiagram.StageDiagram_ID, L_StageDiagram.StageDiagram_Name , L_StageLinks.StageLink_FromID, L_CaseStages.CaseStage_Title as StageFrom,")
            .Append("L_StageLinks.StageLink_ToID, L_CaseStages_To.CaseStage_Title as StageTo, L_StageLinks.StageLink_Position, Case L_StageLinks.StageLink_CustomerType when 1 then 'Private' when 2 then 'Corporate' else 'All' end as Ctype,")
            .Append("Case L_StageLinks.StageLink_Admin when 1 then 'Admin' else '' end as Admin, L_CaseStages_To.CaseStage_DTColumn, Case L_CaseStages_To.CaseStage_DTOverwrite when 1 then 'Overwrite' else '' end as OverWrite ")
            .Append("FROM L_StageDiagram INNER JOIN L_StageLinks ON L_StageDiagram.StageDiagram_ID = L_StageLinks.StageLink_DiagramID INNER JOIN ")
            .Append("L_CaseStages ON L_StageLinks.StageLink_FromID = L_CaseStages.CaseStage_Number INNER JOIN ")
            .Append("L_CaseStages L_CaseStages_To ON L_StageLinks.StageLink_ToID = L_CaseStages_To.CaseStage_Number")
        End With

        Return SQL.ToString

    End Function

    Private Function SQL_PriceList() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT L_Products.Product_Name, MAX(L_ProductTypes.ProductType_Name) AS Type, SUM(D_DefCharges.DefCharge_Amount) AS Net,")
            .Append("SUM(D_DefCharges.DefCharge_TPCost) AS TPCost, SUM(D_DefCharges.DefCharge_VAT / 100 * D_DefCharges.DefCharge_Amount) AS VAT,")
            .Append("SUM(D_DefCharges.DefCharge_VAT / 100 * D_DefCharges.DefCharge_Amount + D_DefCharges.DefCharge_Amount) AS Gross,")
            .Append("SUM(D_DefCharges.DefCharge_Amount - D_DefCharges.DefCharge_TPCost) AS Income ")
            .Append("FROM L_Products INNER JOIN ")
            .Append("L_ProductTypes ON L_Products.Product_Type = L_ProductTypes.ProductType_ID LEFT OUTER JOIN ")
            .Append("D_DefCharges ON L_Products.Product_ID = D_DefCharges.DefCharge_ProductID ")
            .Append(" GROUP BY L_Products.Product_Name")
        End With

        Return SQL.ToString

    End Function

    Private Function SQL_EventSummaryConsultant() As String

        Dim SQL As New StringBuilder
        Dim strDate, strConsul As String
        With SQL

            .Append("SELECT D_Event.Event_ID, L_Consultant.Con_Name, D_Event.Event_Description,")
            .Append("CASE D_Event.Event_Category WHEN 1 THEN 0 ELSE 1 END AS Event_Sched, D_Event.Event_DTEntered,")
            .Append("CASE isnull(L_EventTypes.EventType_ID,0) WHEN 0 THEN 1 ELSE 0 END AS Event_Auto ")
            .Append("FROM D_Event INNER JOIN ")
            .Append("L_Consultant ON D_Event.Event_Enteredby = L_Consultant.Con_ID LEFT OUTER JOIN ")
            .Append("L_EventTypes ON D_Event.Event_Description = L_EventTypes.EventType_Name ")
            strDate = Me.ReportCriteria_Dates("D_Event.Event_DTEntered")
            strConsul = Me.ReportCriteria_Consultant("Event_EnteredBy")
            If strDate.Trim = "" And strConsul.Trim = "" Then
            ElseIf strDate.Trim = "" And strConsul.Trim <> "" Then
                .Append(" WHERE " & strConsul)
            ElseIf strDate.Trim <> "" And strConsul.Trim = "" Then
                .Append(" WHERE " & strDate)
            ElseIf strDate.Trim <> "" And strConsul.Trim <> "" Then
                .Append(" WHERE " & strDate & strConsul)
            End If
            '.Append("WHERE " & Me.ReportCriteria_Dates("D_Event.Event_DTEntered"))

            '.Append(Me.ReportCriteria_Consultant("Event_EnteredBy"))

            ''.Append(" order by L_Consultant.Con_Name, D_Event.Event_Description")

        End With

        Return SQL.ToString

    End Function

    Private Function SQL_InvoiceListV2() As String

        Dim SQL As New StringBuilder

        With SQL


            .Append(" SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_Date) AS Inv_Date, MAX(dbo.A_Invoices.Invoice_DateDue) AS DateDue, ")
            .Append(" MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, SUM(dbo.A_Charges.Charge_Amount) AS Net, ")
            .Append(" CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            .Append(" CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            .Append(" MAX(dbo.L_Products.Product_Name) AS Product, MAX(ISNULL(dbo.D_Company.Company_Name, '')) AS CompanyName, ")
            .Append(" MAX(ISNULL(dbo.D_Company.Company_ID, '')) AS CompanyID, SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, ")
            .Append(" SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, MAX(ISNULL(dbo.L_Consultant.Con_Name, '')) AS consultant")
            .Append(" FROM         dbo.A_Invoices INNER JOIN")
            .Append(" dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN")
            .Append(" dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
            .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID")

            .Append(" WHERE " & Me.ReportCriteria_Dates("invoice_date"))

            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("case_concwid"))
            End If

            .Append(Me.ReportCriteria_Product("case_productid"))
            .Append(Me.ReportCriteria_SLA_Accounts)
            .Append(Me.ReportCriteria_Client_Types)

            .Append(" GROUP BY A_Invoices.Invoice_ID,  d_case.case_consaleid , dbo.A_Charges.Charge_Desc ")
            .Append(" HAVING      (dbo.A_Charges.Charge_Desc <> 'Omit in reports')")
            .Append(" ORDER BY d_case.case_consaleid")





            '.Append("SELECT A_Invoices.Invoice_ID, MAX(A_Invoices.Invoice_Date) AS Inv_Date, MAX(A_Invoices.Invoice_DateDue) AS DateDue, ")
            '.Append("MAX(A_Invoices.Invoice_CaseID) AS CaseID, SUM(A_Charges.Charge_Amount) AS Net, ")
            '.Append("Cast(SUM(A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) as money) AS VAT, ")
            '.Append("cast(SUM(A_Charges.Charge_Amount + (A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100)) as money) AS Gross, ")
            '.Append("MAX(D_Person.Person_FName + ' ' + D_Person.Person_LName) AS FullName, ")
            '.Append("MAX(L_Products.Product_Name) AS Product, MAX(isnull(D_Company.Company_Name,'')) as CompanyName , ")
            '.Append("MAX(isnull(D_Company.Company_ID,'')) as CompanyID,")
            '.Append("sum(a_charges.charge_tpcost) as thirdparty, sum(a_charges.charge_amount - a_charges.charge_tpcost) as income,")
            '.Append("max(isnull(l_consultant.con_name,'')) as consultant ")
            '.Append("FROM A_Invoices INNER JOIN D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID INNER JOIN A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID INNER JOIN D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID INNER JOIN L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID ")
            '.Append("inner join l_consultant on l_consultant.con_id = d_case.case_consaleid ")
            '.Append("WHERE " & Me.ReportCriteria_Dates("invoice_date"))

            'If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '    .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            'ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '    .Append(Me.ReportCriteria_Consultant("case_concwid"))
            'End If

            '.Append(Me.ReportCriteria_Product("case_productid"))
            '.Append(Me.ReportCriteria_SLA_Accounts)
            '.Append(Me.ReportCriteria_Client_Types)

            '.Append(" GROUP BY A_Invoices.Invoice_ID,  d_case.case_consaleid ORDER BY d_case.case_consaleid")

        End With

        Return SQL.ToString

    End Function
    Private Function SQL_LeadsProductType() As String
        Dim SQL As New StringBuilder
        With SQL
            .Append("SELECT     TOP 100 PERCENT dbo.L_ProductTypes.ProductType_Name, COUNT(*) AS totalnewleads")
            .Append(" FROM         dbo.D_Case AS c INNER JOIN")
            .Append(" dbo.L_Products ON c.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID")
            .Append(" WHERE " & Me.ReportCriteria_Dates("c.Case_DtEntered"))
            .Append(" GROUP BY dbo.L_ProductTypes.ProductType_Name")
            .Append(" ORDER BY dbo.L_ProductTypes.ProductType_Name")
        End With
            return SQL.ToString
    End Function
    Private Function SQL_CaseAssignedCSM() As String
        Dim SQL As New StringBuilder
        With SQL

            '.Append(" SELECT     dbo.D_Case.Case_ID, dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name AS Product, dbo.L_CaseStages.CaseStage_Title, ")
            '.Append(" dbo.L_Consultant.Con_Name AS CW, dbo.D_Case.Case_DTAssigned AS CaseDTAssigned, L_Consultant_1.Con_Name AS CaseAssignedBy")
            '.Append(" FROM         dbo.D_Case INNER JOIN")
            '.Append(" dbo.L_Consultant ON dbo.D_Case.Case_ConCWID = dbo.L_Consultant.Con_ID INNER JOIN")
            '.Append(" dbo.L_Consultant AS L_Consultant_1 ON dbo.D_Case.Case_AssignedBy = L_Consultant_1.Con_ID INNER JOIN")
            '.Append(" dbo.L_CaseStages ON dbo.D_Case.Case_StatusID = dbo.L_CaseStages.CaseStage_Number INNER JOIN")
            '.Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            '.Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID")
            '.Append(" WHERE " & Me.ReportCriteria_Dates("dbo.D_Case.Case_DTAssigned"))
            'If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '    .Append(Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConSaleID"))
            'ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '    .Append(Me.ReportCriteria_Consultant("dbo.D_Case.case_concwid"))
            'End If
            '.Append(" ORDER BY dbo.L_Consultant.Con_Name")
            Dim strDate, strCon As String

            .Append(" SELECT     TOP 100 PERCENT dbo.D_Case.Case_ID, dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name AS Product, ")
            .Append(" dbo.L_CaseStages.CaseStage_Title, dbo.L_Consultant.Con_Name AS CW, dbo.D_Case.Case_DTAssigned AS CaseDTAssigned, ")
            .Append(" L_Consultant_1.Con_Name AS CaseAssignedBy, dbo.V_Case.GrossInv as InvGross, dbo.V_Case.Paid as paid, dbo.V_Case.Balance as balance")
            .Append(" FROM         dbo.D_Case INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.D_Case.Case_ConCWID = dbo.L_Consultant.Con_ID INNER JOIN")
            .Append(" dbo.L_Consultant AS L_Consultant_1 ON dbo.D_Case.Case_AssignedBy = L_Consultant_1.Con_ID INNER JOIN")
            .Append(" dbo.L_CaseStages ON dbo.D_Case.Case_StatusID = dbo.L_CaseStages.CaseStage_Number INNER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
            .Append(" dbo.V_Case ON dbo.D_Case.Case_ID = dbo.V_Case.Case_ID")
            ' .Append(" WHERE ")
            strDate = Me.ReportCriteria_Dates("dbo.D_Case.Case_DTAssigned")
            If strDate.ToString.Trim = String.Empty Then
                If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                    strCon = Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConSaleID", True)
                    If strCon <> String.Empty Then
                        .Append(" WHERE " & strCon)

                    Else
                        .Append(strCon)
                    End If
                ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                    strCon = Me.ReportCriteria_Consultant("dbo.D_Case.case_concwid")
                    If strCon <> String.Empty Then


                    Else
                        .Append(strCon)

                    End If
                End If
            Else
                If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                    strCon = Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConSaleID")

                    If strCon <> String.Empty Then
                        .Append(" WHERE " & strDate & strCon)

                    Else
                        .Append(strCon)
                    End If
                ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                    strCon = Me.ReportCriteria_Consultant("dbo.D_Case.case_concwid")
                    If strCon <> String.Empty Then
                        .Append(" WHERE " & strDate & strCon)
                    Else
                        .Append(strCon)
                    End If
                End If
            End If

            .Append(" ORDER BY dbo.L_Consultant.Con_Name")




            '.Append(" WHERE     (dbo.D_Case.Case_DTAssigned BETWEEN CONVERT(DATETIME, '2009-04-20 00:00:00', 102) AND CONVERT(DATETIME, '2009-04-30 00:00:00', 102))")
            '.Append(" SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_Date) AS Inv_Date, MAX(dbo.A_Invoices.Invoice_DateDue) AS DateDue, ")
            '.Append(" MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, SUM(dbo.A_Charges.Charge_Amount) AS Net, ")
            '.Append(" CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            '.Append(" CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            '.Append(" MAX(dbo.L_Products.Product_Name) AS Product, MAX(ISNULL(dbo.D_Company.Company_Name, '')) AS CompanyName, ")
            '.Append(" MAX(ISNULL(dbo.D_Company.Company_ID, '')) AS CompanyID, SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, ")
            '.Append(" SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, MAX(ISNULL(dbo.L_Consultant.Con_Name, '')) AS consultant, ")
            '.Append(" dbo.V_Case.Paid as balance")
            '.Append(" FROM         dbo.A_Invoices INNER JOIN")
            '.Append(" dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN")
            '.Append(" dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN")
            '.Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
            '.Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN")
            '.Append(" dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID INNER JOIN")
            '.Append(" dbo.V_Case ON dbo.D_Case.Case_ID = dbo.V_Case.Case_ID")
            '.Append(" WHERE " & Me.ReportCriteria_Dates("invoice_date"))

            'If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '    .Append(Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConSaleID"))
            'ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '    .Append(Me.ReportCriteria_Consultant("dbo.D_Case.case_concwid"))
            'End If

            '.Append(Me.ReportCriteria_Product("dbo.D_Case.case_productid"))
            '.Append(Me.ReportCriteria_SLA_Accounts)
            '.Append(Me.ReportCriteria_Client_Types)

            '.Append("GROUP BY dbo.A_Invoices.Invoice_ID, dbo.A_Invoices.Invoice_CaseID, dbo.D_Case.Case_ConSaleID, dbo.A_Charges.Charge_Desc, ")
            '.Append("dbo.V_Case.Paid")
            '.Append(" HAVING      (dbo.A_Charges.Charge_Desc <> 'Omit in reports')")
            '.Append("ORDER BY MAX(dbo.A_Invoices.Invoice_CaseID)")
        End With
        Return SQL.ToString
    End Function
    Private Function SQL_InvoiceListCase() As String

        Dim SQL As New StringBuilder

        With SQL



            .Append(" SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_Date) AS Inv_Date, MAX(dbo.A_Invoices.Invoice_DateDue) AS DateDue, ")
            .Append(" MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, SUM(dbo.A_Charges.Charge_Amount) AS Net, ")
            .Append(" CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            .Append(" CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            .Append(" MAX(dbo.L_Products.Product_Name) AS Product, MAX(ISNULL(dbo.D_Company.Company_Name, '')) AS CompanyName, ")
            .Append(" MAX(ISNULL(dbo.D_Company.Company_ID, '')) AS CompanyID, SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, ")
            .Append(" SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, MAX(ISNULL(dbo.L_Consultant.Con_Name, '')) AS consultant, ")
            .Append(" dbo.V_Case.Paid as balance")
            .Append(" FROM         dbo.A_Invoices INNER JOIN")
            .Append(" dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN")
            .Append(" dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
            .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID INNER JOIN")
            .Append(" dbo.V_Case ON dbo.D_Case.Case_ID = dbo.V_Case.Case_ID")
            .Append(" WHERE " & Me.ReportCriteria_Dates("invoice_date"))

            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConSaleID"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("dbo.D_Case.case_concwid"))
            End If

            .Append(Me.ReportCriteria_Product("dbo.D_Case.case_productid"))
            .Append(Me.ReportCriteria_SLA_Accounts)
            .Append(Me.ReportCriteria_Client_Types)

            .Append("GROUP BY dbo.A_Invoices.Invoice_ID, dbo.A_Invoices.Invoice_CaseID, dbo.D_Case.Case_ConSaleID, dbo.A_Charges.Charge_Desc, ")
            .Append("dbo.V_Case.Paid")
            .Append(" HAVING      (dbo.A_Charges.Charge_Desc <> 'Omit in reports')")
            .Append("ORDER BY MAX(dbo.A_Invoices.Invoice_CaseID)")


            '.Append(" SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_Date) AS Inv_Date, MAX(dbo.A_Invoices.Invoice_DateDue) AS DateDue, ")
            '.Append(" MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, SUM(dbo.A_Charges.Charge_Amount) AS Net, ")
            '.Append(" CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            '.Append(" CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            '.Append(" MAX(dbo.L_Products.Product_Name) AS Product, MAX(ISNULL(dbo.D_Company.Company_Name, '')) AS CompanyName, ")
            '.Append(" MAX(ISNULL(dbo.D_Company.Company_ID, '')) AS CompanyID, SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, ")
            '.Append(" SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, MAX(ISNULL(dbo.L_Consultant.Con_Name, '')) AS consultant")
            '.Append(" FROM         dbo.A_Invoices INNER JOIN")
            '.Append(" dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN")
            '.Append(" dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN")
            '.Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
            '.Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN")
            '.Append(" dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID")

            '.Append(" WHERE " & Me.ReportCriteria_Dates("invoice_date"))

            'If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '    .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            'ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '    .Append(Me.ReportCriteria_Consultant("case_concwid"))
            'End If

            '.Append(Me.ReportCriteria_Product("case_productid"))
            '.Append(Me.ReportCriteria_SLA_Accounts)
            '.Append(Me.ReportCriteria_Client_Types)

            '.Append(" GROUP BY A_Invoices.Invoice_ID,  d_case.case_consaleid , dbo.A_Charges.Charge_Desc ")
            '.Append(" HAVING      (dbo.A_Charges.Charge_Desc <> 'Omit in reports')")
            '.Append(" ORDER BY d_case.case_consaleid")





            '.Append("SELECT A_Invoices.Invoice_ID, MAX(A_Invoices.Invoice_Date) AS Inv_Date, MAX(A_Invoices.Invoice_DateDue) AS DateDue, ")
            '.Append("MAX(A_Invoices.Invoice_CaseID) AS CaseID, SUM(A_Charges.Charge_Amount) AS Net, ")
            '.Append("Cast(SUM(A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) as money) AS VAT, ")
            '.Append("cast(SUM(A_Charges.Charge_Amount + (A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100)) as money) AS Gross, ")
            '.Append("MAX(D_Person.Person_FName + ' ' + D_Person.Person_LName) AS FullName, ")
            '.Append("MAX(L_Products.Product_Name) AS Product, MAX(isnull(D_Company.Company_Name,'')) as CompanyName , ")
            '.Append("MAX(isnull(D_Company.Company_ID,'')) as CompanyID,")
            '.Append("sum(a_charges.charge_tpcost) as thirdparty, sum(a_charges.charge_amount - a_charges.charge_tpcost) as income,")
            '.Append("max(isnull(l_consultant.con_name,'')) as consultant ")
            '.Append("FROM A_Invoices INNER JOIN D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID INNER JOIN A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID INNER JOIN D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID INNER JOIN L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID ")
            '.Append("inner join l_consultant on l_consultant.con_id = d_case.case_consaleid ")
            '.Append("WHERE " & Me.ReportCriteria_Dates("invoice_date"))

            'If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            '    .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            'ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            '    .Append(Me.ReportCriteria_Consultant("case_concwid"))
            'End If

            '.Append(Me.ReportCriteria_Product("case_productid"))
            '.Append(Me.ReportCriteria_SLA_Accounts)
            '.Append(Me.ReportCriteria_Client_Types)

            '.Append(" GROUP BY A_Invoices.Invoice_ID,  d_case.case_consaleid ORDER BY d_case.case_consaleid")

        End With

        Return SQL.ToString

    End Function

    Private Function GetCurrency(ByVal caseid As Long) As String
        Dim SQL As New StringBuilder
        Dim strCurr As Object
        With SQL
            .Append(" Select dbo.L_Country.Country_Symbol")
            .Append(" FROM         dbo.L_Country INNER JOIN")
            .Append(" dbo.L_Branch ON dbo.L_Country.Country_ID = dbo.L_Branch.Branch_CountryID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Branch.Branch_ID = dbo.L_ProductTypes.ProductType_BranchID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.L_ProductTypes.ProductType_ID = dbo.L_Products.Product_Type INNER JOIN")
            .Append(" dbo.D_Case ON dbo.L_Products.Product_ID = dbo.D_Case.Case_ProductID")
            .Append(" WHERE(dbo.D_Case.Case_ID = " & caseid & ")")
            strCurr = Data.GetScalar(.ToString)
        End With
        If Not strCurr Is Nothing Then
            Return strCurr.ToString
        Else
            Return ""
        End If
    End Function
    Private Function SQL_NoEventCases() As String

        Dim SQL As New StringBuilder
        Dim dateTime As String
        dateTime = Format(Date.Now.AddDays(-14), "yyyy-mm-dd hh:mm:ss")
        'Dim dt As Date = Format(dateTime, "yyyy-mm-dd")
        With SQL
            .Append("SELECT     dbo.D_Case.Case_ID, MAX(dbo.D_Event.Event_DTEntered) AS LastEventOn, dbo.L_Consultant.Con_Name, dbo.V_Case.Gross, dbo.V_Case.Paid,   ")
            .Append(" dbo.V_Case.Balance ")
            .Append("  FROM  dbo.D_Case INNER JOIN    dbo.D_Event ON dbo.D_Case.Case_ID = dbo.D_Event.Event_LinkID INNER JOIN ")
            .Append(" dbo.L_Consultant ON dbo.D_Case.Case_ConSaleID = dbo.L_Consultant.Con_ID INNER JOIN   dbo.L_Branch ON dbo.L_Consultant.Con_BranchID = dbo.L_Branch.Branch_ID INNER JOIN")
            .Append(" dbo.V_Case ON dbo.D_Case.Case_ID = dbo.V_Case.Case_ID GROUP BY dbo.L_Branch.Branch_ID, dbo.D_Case.Case_ID, dbo.D_Case.Case_StatusID, dbo.L_Consultant.Con_Name, dbo.V_Case.Balance, dbo.V_Case.Gross, ")
            .Append("   dbo.V_Case.Paid, ")
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append("dbo.D_Case.Case_ConSaleID")
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append("dbo.D_Case.Case_ConCWID")
            End If
            .Append(" HAVING      (MAX(dbo.D_Event.Event_DTEntered) <= DATEADD(day, - 14, GETDATE())) AND (dbo.D_Case.Case_StatusID > 100) AND    (dbo.D_Case.Case_StatusID < 1000) ")
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConSaleID"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("dbo.D_Case.case_concwid"))
            End If
        End With

        Return SQL.ToString


    End Function
    Private Function SQL_WorkInProgress() As String
        Dim SQL As New StringBuilder
        With SQL

            .Append(" SELECT     V_Case.Case_ID, V_Case.Balance, CONVERT(VARCHAR(10),V_InvoiceSimple.DueDate,111) as DueDate, V_InvoiceSimple.Invoice_ID, V_InvoiceSimple.Gross AS InvAmount, ")
            .Append(" V_Case.CaseStage_Title, V_Case.Paid, V_Case.Candidate, V_Case.Case_CompanyID, D_Company.Company_Name, V_Case.Case_ActiveStatus, ")
            .Append(" V_Case.Case_ConCWID, V_Case.Case_ConSaleID, V_Case.Case_ProductID, ISNULL(L_Products.Product_Name, 'Unspecified') AS Product,CASE ")
            .Append(" When V_Case.Paid <=  ISNULL(PrevInv.Gross, 0) + ISNULL(InvCredits.GrossCredit, 0) then V_InvoiceSimple.Gross")
            .Append(" When V_Case.Paid >=  ISNULL(PrevInv.Gross, 0) + ISNULL(InvCredits.GrossCredit, 0) + V_InvoiceSimple.Gross then 0")
            .Append(" else  (ISNULL(PrevInv.Gross, 0) + ISNULL(InvCredits.GrossCredit, 0) + V_InvoiceSimple.Gross) - V_Case.Paid end as InvOS,")
            .Append(" Case Case_CompanyID when 0 then 'Private Individual' else Company_Name end as Client,")
            .Append(" Case Case_CompanyID when 0 then '' else cast (Company_ID as varchar) end as ClientID,")
            .Append(" V_case.Case_CandidateID,")
            .Append(" V_Case.Case_DTStatus")
            .Append(" FROM         V_InvoiceSimple INNER JOIN")
            .Append(" V_Case ON V_InvoiceSimple.CaseID = V_Case.Case_ID LEFT OUTER JOIN")
            .Append(" L_Products ON V_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN")
            .Append(" D_Company ON V_Case.Case_CompanyID = D_Company.Company_ID LEFT OUTER JOIN")
            .Append(" (SELECT     A_Invoices.Invoice_ID, SUM(V_InvoiceSimple.Gross) AS Gross")
            .Append(" FROM          A_Invoices INNER JOIN")
            .Append(" V_InvoiceSimple ON A_Invoices.Invoice_ID > V_InvoiceSimple.Invoice_ID AND ")
            .Append(" A_Invoices.Invoice_CaseID = V_InvoiceSimple.CaseID")
            .Append(" GROUP BY A_Invoices.Invoice_ID) PrevInv ON V_InvoiceSimple.Invoice_ID = PrevInv.Invoice_ID LEFT OUTER JOIN")
            .Append(" (SELECT     Invoice_ID, SUM(Gross) AS GrossCredit")
            .Append(" FROM V_InvoiceSimple            WHERE(Gross < 0)")
            .Append(" GROUP BY Invoice_ID) InvCredits ON V_InvoiceSimple.Invoice_ID = InvCredits.Invoice_ID  WHERE (NOT (V_Case.Balance BETWEEN - .01 AND .01)) AND (V_InvoiceSimple.Gross > 0) AND (V_InvoiceSimple.DueDate < GETDATE()) AND ")
            .Append(" (V_InvoiceSimple.Gross + ISNULL(PrevInv.Gross, 0) - V_Case.Paid - ISNULL(InvCredits.GrossCredit, 0) > 0.1)  ")
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("case_concwid"))
            End If
        End With

        Return SQL.ToString
    End Function

    Private Function SQL_InvoiceList() As String()

        'Invoice List By Consultant (202)
        Dim dats As String
        Dim SQL As New StringBuilder
        Dim arrSQL(1) As String
        With SQL
            .Append("SELECT A_Invoices.Invoice_ID, MAX(A_Invoices.Invoice_Date) AS Inv_Date, MAX(A_Invoices.Invoice_DateDue) AS DateDue, MAX(A_Invoices.Invoice_CaseID) ")
            .Append("AS CaseID, SUM(A_Charges.Charge_Amount) AS Net, SUM(A_Charges.Charge_Amount - A_Charges.Charge_TPCost) AS Income, CAST(SUM(A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            .Append("CAST(SUM(A_Charges.Charge_Amount + A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            .Append("MAX(D_Person.Person_FName + ' ' + D_Person.Person_LName) AS FullName, MAX(L_Products.Product_Name) AS Product, ")
            .Append("MAX(ISNULL(D_Company.Company_Name, 'Private Individuals')) AS CompanyName, MAX(ISNULL(D_Company.Company_ID, '')) AS CompanyID, ")
            .Append("Max(L_Consultant.Con_Name) as SaleCon ")
            .Append("from A_Invoices INNER JOIN ")
            .Append("A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID LEFT OUTER JOIN ")
            .Append("D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID LEFT OUTER JOIN ")
            .Append("L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN ")
            .Append("D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID LEFT OUTER JOIN ")
            .Append("L_Consultant ON D_Case.Case_ConSaleID = L_Consultant.Con_ID LEFT OUTER JOIN ")
            .Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID ")
            .Append("WHERE ") ' (Invoice_Date BETWEEN '06/03/2006 23:59:59' AND '06/10/2006 23:59:59') 
            dats = Me.ReportCriteria_Dates("invoice_date")
            .Append(dats)
            If dats = "" Then
                If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                    .Append(Me.ReportCriteria_Consultant("case_consaleid", True))
                ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                    .Append(Me.ReportCriteria_Consultant("case_concwid", True))
                End If
            Else
                If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                    .Append(Me.ReportCriteria_Consultant("case_consaleid", False))
                ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                    .Append(Me.ReportCriteria_Consultant("case_concwid"))
                End If
            End If

            '.Append(Me.ReportCriteria_Consultant("case_consaleid"))
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("case_consaleid", False))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("case_concwid"))
            End If

            .Append(Me.ReportCriteria_Product("case_productid"))
            .Append(Me.ReportCriteria_SLA_Accounts)
            .Append(Me.ReportCriteria_Client_Types)

            .Append(" GROUP BY A_Invoices.Invoice_ID HAVING      (SUM(dbo.A_Charges.Charge_Amount) <> 0) ORDER BY A_Invoices.Invoice_ID")
        End With
        arrSQL(0) = SQL.ToString
        SQL = New StringBuilder
        With SQL


            .Append("SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_Date) AS Inv_Date, MAX(dbo.A_Invoices.Invoice_DateDue) AS DateDue, ")
            .Append(" MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, SUM(dbo.A_Charges.Charge_Amount) AS Net, ")
            .Append(" SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS Income, dbo.A_Charges.charge_dummygross AS VAT, ")
            .Append(" dbo.A_Charges.charge_dummygross AS Gross, MAX(dbo.D_Person.Person_FName + ' ' + dbo.D_Person.Person_LName) AS FullName, ")
            .Append(" MAX(dbo.L_Products.Product_Name) AS Product, MAX(ISNULL(dbo.D_Company.Company_Name, 'Private Individuals')) AS CompanyName, ")
            .Append(" MAX(ISNULL(dbo.D_Company.Company_ID, '')) AS CompanyID, MAX(dbo.L_Consultant.Con_Name) AS SaleCon")
            .Append(" FROM         dbo.A_Invoices INNER JOIN")
            .Append(" dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID LEFT OUTER JOIN")
            .Append(" dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID LEFT OUTER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
            .Append(" dbo.D_Person ON dbo.D_Case.Case_CandidateID = dbo.D_Person.Person_ID LEFT OUTER JOIN")
            .Append("                       dbo.L_Consultant ON dbo.D_Case.Case_ConSaleID = dbo.L_Consultant.Con_ID LEFT OUTER JOIN")
            .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID")
            .Append(" WHERE")
            dats = Me.ReportCriteria_Dates("invoice_date")
            .Append(dats)
            If dats = "" Then
                If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                    .Append(Me.ReportCriteria_Consultant("case_consaleid", True))
                ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                    .Append(Me.ReportCriteria_Consultant("case_concwid", True))
                End If
            Else
                If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                    .Append(Me.ReportCriteria_Consultant("case_consaleid", False))
                ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                    .Append(Me.ReportCriteria_Consultant("case_concwid"))
                End If
            End If

            .Append(" GROUP BY dbo.A_Invoices.Invoice_ID, dbo.A_Charges.Charge_Desc, dbo.A_Charges.charge_dummygross, ")
            .Append(" dbo.A_Charges.charge_dummygross * 100 / 2.5")
            .Append(" HAVING      (SUM(dbo.A_Charges.Charge_Amount) = 0) AND (dbo.A_Charges.Charge_Desc LIKE 'Credited charge 2.5 VAT')")
            .Append(" ORDER BY dbo.A_Invoices.Invoice_ID")
        End With
        arrSQL(1) = SQL.ToString
        Return arrSQL

    End Function
    Private Function SQL_InvoiceList1() As String

        'Invoice List By Consultant (202)
        Dim dats As String
        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT A_Invoices.Invoice_ID, MAX(A_Invoices.Invoice_Date) AS Inv_Date, MAX(A_Invoices.Invoice_DateDue) AS DateDue, MAX(A_Invoices.Invoice_CaseID) ")
            .Append("AS CaseID, SUM(A_Charges.Charge_Amount) AS Net, SUM(A_Charges.Charge_Amount - A_Charges.Charge_TPCost) AS Income, CAST(SUM(A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) AS money) AS VAT, ")
            .Append("CAST(SUM(A_Charges.Charge_Amount + A_Charges.Charge_Amount * A_Charges.Charge_VAT / 100) AS money) AS Gross, ")
            .Append("MAX(D_Person.Person_FName + ' ' + D_Person.Person_LName) AS FullName, MAX(L_Products.Product_Name) AS Product, ")
            .Append("MAX(ISNULL(D_Company.Company_Name, 'Private Individuals')) AS CompanyName, MAX(ISNULL(D_Company.Company_ID, '')) AS CompanyID, ")
            .Append("Max(L_Consultant.Con_Name) as SaleCon ")
            .Append("from A_Invoices INNER JOIN ")
            .Append("A_Charges ON A_Invoices.Invoice_ID = A_Charges.Charge_InvoiceID LEFT OUTER JOIN ")
            .Append("D_Case ON A_Invoices.Invoice_CaseID = D_Case.Case_ID LEFT OUTER JOIN ")
            .Append("L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN ")
            .Append("D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID LEFT OUTER JOIN ")
            .Append("L_Consultant ON D_Case.Case_ConSaleID = L_Consultant.Con_ID LEFT OUTER JOIN ")
            .Append("D_Company ON D_Case.Case_CompanyID = D_Company.Company_ID ")
            .Append("WHERE ") ' (Invoice_Date BETWEEN '06/03/2006 23:59:59' AND '06/10/2006 23:59:59') 
            dats = Me.ReportCriteria_Dates("invoice_date")
            .Append(dats)
            If dats = "" Then
                If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                    .Append(Me.ReportCriteria_Consultant("case_consaleid", True))
                ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                    .Append(Me.ReportCriteria_Consultant("case_concwid", True))
                End If
            Else
                If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                    .Append(Me.ReportCriteria_Consultant("case_consaleid", False))
                ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                    .Append(Me.ReportCriteria_Consultant("case_concwid"))
                End If
            End If

            '.Append(Me.ReportCriteria_Consultant("case_consaleid"))
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("case_consaleid", False))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("case_concwid"))
            End If

            .Append(Me.ReportCriteria_Product("case_productid"))
            .Append(Me.ReportCriteria_SLA_Accounts)
            .Append(Me.ReportCriteria_Client_Types)

            .Append(" GROUP BY A_Invoices.Invoice_ID ORDER BY A_Invoices.Invoice_ID")
        End With

        Return SQL.ToString


    End Function
    Private Function SQL_casesSubmitted() As String

        'Invoice List By Consultant (202)

        Dim SQL As New StringBuilder

        With SQL
            .Append(" SELECT     TOP 100 PERCENT dbo.D_Case.Case_ID, ISNULL(dbo.L_Products.Product_Name, 'unspecified') AS Product, dbo.L_CaseStages.CaseStage_Title, ")
            .Append(" dbo.D_Case.Case_DTSold, ISNULL(L_Consultant_Sales.Con_Name, 'Unspecified') AS SalesCon, ISNULL(L_Consultant_CW.Con_Name, 'Unspecified') ")
            .Append(" AS CW, dbo.V_Case.Case_DTSubmitted")
            .Append(" FROM         dbo.L_Consultant AS L_Consultant_CW RIGHT OUTER JOIN")
            .Append(" dbo.V_Case RIGHT OUTER JOIN")
            .Append(" dbo.D_Case ON dbo.V_Case.Case_ID = dbo.D_Case.Case_ID LEFT OUTER JOIN")
            .Append(" dbo.A_Invoices ON dbo.D_Case.Case_ID = dbo.A_Invoices.Invoice_CaseID LEFT OUTER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
            .Append(" dbo.L_CaseStages ON dbo.D_Case.Case_StatusID = dbo.L_CaseStages.CaseStage_Number ON ")
            .Append(" L_Consultant_CW.Con_ID = dbo.D_Case.Case_ConCWID LEFT OUTER JOIN")
            .Append(" dbo.L_Consultant AS L_Consultant_Sales ON dbo.D_Case.Case_ConSaleID = L_Consultant_Sales.Con_ID LEFT OUTER JOIN")
            .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID")


            .Append(" WHERE ")
            .Append(Me.ReportCriteria_Dates("dbo.V_Case.Case_DTSubmitted"))

            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("dbo.D_Case.case_consaleid"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConCWID"))
            End If



            .Append(" ORDER BY dbo.V_Case.Case_DTSubmitted, dbo.D_Case.Case_ID")
        End With

        Return SQL.ToString

    End Function
    Private Function SQL_MonthlySalesReport() As String
        Dim SQL As New StringBuilder
        With SQL


            .Append(" SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, MAX(ISNULL(dbo.L_Consultant.Con_Name, ''))  ")
            .Append("    AS consultant, dbo.L_ProductTypes.ProductType_Name, MAX(dbo.L_Products.Product_Name) AS Product,  ")
            .Append("   MAX(ISNULL(dbo.D_Company.Company_Name, 'Private Individual')) AS CompanyName, SUM(dbo.A_Charges.Charge_Amount) AS Net,  ")
            .Append("   CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT,  ")
            .Append("     CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross,  ")
            .Append("    SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, ")
            .Append("    dbo.A_Invoices.Invoice_Date")
            .Append(" FROM         dbo.A_Invoices INNER JOIN")
            .Append(" dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN")
            .Append(" dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
            .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID")


            .Append(" WHERE " & Me.ReportCriteria_DatesMonthly("invoice_date"))

            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("case_concwid"))
            End If

            .Append(Me.ReportCriteria_Product("case_productid"))
            .Append(Me.ReportCriteria_SLA_Accounts)
            .Append(Me.ReportCriteria_Client_Types)


            .Append(" GROUP BY dbo.A_Invoices.Invoice_ID, dbo.D_Case.Case_ConSaleID, dbo.L_ProductTypes.ProductType_Name, dbo.A_Invoices.Invoice_Date")
            .Append(" ORDER BY dbo.D_Case.Case_ConSaleID")



           
        End With

        Return SQL.ToString
    End Function

    Private Function SQL_WeeklySalesReport() As String
        Dim SQL As New StringBuilder
        With SQL

        .Append(" SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, MAX(ISNULL(dbo.L_Consultant.Con_Name, ''))  ")
        .Append("    AS consultant, dbo.L_ProductTypes.ProductType_Name, MAX(dbo.L_Products.Product_Name) AS Product,  ")
        .Append("   MAX(ISNULL(dbo.D_Company.Company_Name, 'Private Individual')) AS CompanyName, SUM(dbo.A_Charges.Charge_Amount) AS Net,  ")
        .Append("   CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT,  ")
        .Append("     CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross,  ")
        .Append("    SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, ")
        .Append("    dbo.A_Invoices.Invoice_Date")
        .Append(" FROM         dbo.A_Invoices INNER JOIN")
        .Append(" dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN")
        .Append(" dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN")
        .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
        .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN")
        .Append(" dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID INNER JOIN")
        .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID")


        .Append(" WHERE " & Me.ReportCriteria_DatesWeekly("invoice_date"))

        If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            .Append(Me.ReportCriteria_Consultant("case_consaleid"))
        ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            .Append(Me.ReportCriteria_Consultant("case_concwid"))
        End If

        .Append(Me.ReportCriteria_Product("case_productid"))
        .Append(Me.ReportCriteria_SLA_Accounts)
        .Append(Me.ReportCriteria_Client_Types)


        .Append(" GROUP BY dbo.A_Invoices.Invoice_ID, dbo.D_Case.Case_ConSaleID, dbo.L_ProductTypes.ProductType_Name, dbo.A_Invoices.Invoice_Date")
        .Append(" ORDER BY dbo.D_Case.Case_ConSaleID")




        End With
        Return SQL.ToString
    End Function
    Private Function SQL_DailySalesReport() As String
        Dim SQL As New StringBuilder
        With SQL

            .Append(" SELECT     TOP 100 PERCENT dbo.A_Invoices.Invoice_ID, MAX(dbo.A_Invoices.Invoice_CaseID) AS CaseID, MAX(ISNULL(dbo.L_Consultant.Con_Name, ''))  ")
        .Append("    AS consultant, dbo.L_ProductTypes.ProductType_Name, MAX(dbo.L_Products.Product_Name) AS Product,  ")
        .Append("   MAX(ISNULL(dbo.D_Company.Company_Name, 'Private Individual')) AS CompanyName, SUM(dbo.A_Charges.Charge_Amount) AS Net,  ")
        .Append("   CAST(SUM(dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS VAT,  ")
        .Append("     CAST(SUM(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * dbo.A_Charges.Charge_VAT / 100) AS money) AS Gross,  ")
        .Append("    SUM(dbo.A_Charges.Charge_TPCost) AS thirdparty, SUM(dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost) AS income, ")
        .Append("    dbo.A_Invoices.Invoice_Date")
        .Append(" FROM         dbo.A_Invoices INNER JOIN")
        .Append(" dbo.D_Case ON dbo.A_Invoices.Invoice_CaseID = dbo.D_Case.Case_ID INNER JOIN")
        .Append(" dbo.A_Charges ON dbo.A_Invoices.Invoice_ID = dbo.A_Charges.Charge_InvoiceID INNER JOIN")
        .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID LEFT OUTER JOIN")
        .Append(" dbo.D_Company ON dbo.D_Case.Case_CompanyID = dbo.D_Company.Company_ID INNER JOIN")
        .Append(" dbo.L_Consultant ON dbo.L_Consultant.Con_ID = dbo.D_Case.Case_ConSaleID INNER JOIN")
        .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID")


            .Append(" WHERE " & Me.ReportCriteria_Dates("invoice_date"))

        If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
            .Append(Me.ReportCriteria_Consultant("case_consaleid"))
        ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
            .Append(Me.ReportCriteria_Consultant("case_concwid"))
        End If

        .Append(Me.ReportCriteria_Product("case_productid"))
        .Append(Me.ReportCriteria_SLA_Accounts)
        .Append(Me.ReportCriteria_Client_Types)


        .Append(" GROUP BY dbo.A_Invoices.Invoice_ID, dbo.D_Case.Case_ConSaleID, dbo.L_ProductTypes.ProductType_Name, dbo.A_Invoices.Invoice_Date")
        .Append(" ORDER BY dbo.D_Case.Case_ConSaleID")




        End With
        Return SQL.ToString
    End Function
    Private Function SQL_FeedbackReport() As String
        'Dim SQL As New StringBuilder

        'With SQL
        '    .Append(" SELECT     TOP 100 PERCENT dbo.V_Case.Case_ID as [Case ID], dbo.L_Consultant.Con_Name as [Sales Consultant], dbo.D_Feedback.Feedback_DTEntered as [Feedback date], ")
        '    .Append(" dbo.L_ProductTypes.ProductType_Name as [Product Type], dbo.L_Products.Product_Name as [Product Name], dbo.V_Case.CaseStage_Title as [Case Stage], dbo.D_FeedbackText.FeedbackText_Text as [Feedback comments from the client]")
        '    .Append(" FROM         dbo.D_Feedback INNER JOIN")
        '    .Append(" dbo.D_FeedbackText ON dbo.D_Feedback.Feedback_ID = dbo.D_FeedbackText.FeedbackText_FeedbackID INNER JOIN")
        '    .Append(" dbo.V_Case ON dbo.D_Feedback.Feedback_CaseID = dbo.V_Case.Case_ID INNER JOIN")
        '    .Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConSaleID = dbo.L_Consultant.Con_ID INNER JOIN")
        '    .Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
        '    .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID WHERE")
        '    .Append(Me.ReportCriteria_Dates("D_Feedback.Feedback_DTEntered "))
        '    .Append(" order by d_Feedback.Feedback_DTEntered desc")
        'End With

        'Return SQL.ToString
        Dim SQL As New StringBuilder

        With SQL
            '.Append(" SELECT     TOP 100 PERCENT dbo.V_Case.Case_ID AS [Case ID], dbo.L_Consultant.Con_Name AS [Sales Consultant], ")
            '.Append(" dbo.D_Feedback.Feedback_DTEntered AS [Feedback date], dbo.L_ProductTypes.ProductType_Name AS [Product Type], ")
            '.Append(" dbo.L_Products.Product_Name AS [Product Name], dbo.V_Case.CaseStage_Title AS [Case Stage], ")
            '.Append(" dbo.D_FeedbackText.FeedbackText_Text AS [Feedback comments from the client]")
            '.Append(" FROM         dbo.D_Feedback INNER JOIN")
            '.Append(" dbo.D_FeedbackText ON dbo.D_Feedback.Feedback_ID = dbo.D_FeedbackText.FeedbackText_FeedbackID INNER JOIN")
            '.Append(" dbo.V_Case ON dbo.D_Feedback.Feedback_CaseID = dbo.V_Case.Case_ID INNER JOIN")
            '.Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConSaleID = dbo.L_Consultant.Con_ID INNER JOIN")
            '.Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            '.Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID WHERE")
            '.Append(Me.ReportCriteria_Dates("D_Feedback.Feedback_DTEntered "))
            '.Append(" AND (dbo.V_Case.Case_StatusID <> 1030) ORDER BY dbo.D_Feedback.Feedback_DTEntered DESC")
            .Append(" SELECT     TOP 100 PERCENT dbo.V_Case.Case_ID AS [Case ID], dbo.L_Consultant.Con_Name AS [Sales Consultant], L_Consultant_1.Con_Name AS CSM, ")
            .Append(" dbo.D_Feedback.Feedback_DTEntered AS [Feedback date], dbo.L_ProductTypes.ProductType_Name AS [Product Type], ")
            .Append(" dbo.L_Products.Product_Name AS [Product Name], dbo.V_Case.CaseStage_Title AS [Case Stage], ")
            .Append(" dbo.D_FeedbackText.FeedbackText_Text AS [Feedback comments from the client]")
            .Append(" FROM         dbo.D_Feedback INNER JOIN")
            .Append(" dbo.D_FeedbackText ON dbo.D_Feedback.Feedback_ID = dbo.D_FeedbackText.FeedbackText_FeedbackID INNER JOIN")
            .Append(" dbo.V_Case ON dbo.D_Feedback.Feedback_CaseID = dbo.V_Case.Case_ID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConSaleID = dbo.L_Consultant.Con_ID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
            .Append(" dbo.L_Consultant AS L_Consultant_1 ON dbo.V_Case.Case_ConCWID = L_Consultant_1.Con_ID WHERE")
            .Append(Me.ReportCriteria_Dates("D_Feedback.Feedback_DTEntered "))
            .Append(" AND (dbo.V_Case.Case_StatusID <> 1030) ORDER BY dbo.D_Feedback.Feedback_DTEntered DESC")
        End With
        Return SQL.ToString
    End Function


    Private Function SQL_FeedbackReport_CancelledPresale() As String
        Dim SQL As New StringBuilder

        With SQL
            .Append(" SELECT     TOP 100 PERCENT dbo.V_Case.Case_ID AS [Case ID], dbo.L_Consultant.Con_Name AS [Sales Consultant], ")
            .Append(" dbo.D_Feedback.Feedback_DTEntered AS [Feedback date], dbo.L_ProductTypes.ProductType_Name AS [Product Type], ")
            .Append(" dbo.L_Products.Product_Name AS [Product Name], dbo.V_Case.CaseStage_Title AS [Case Stage], ")
            .Append(" dbo.D_FeedbackText.FeedbackText_Text AS [Feedback comments from the client]")
            .Append(" FROM         dbo.D_Feedback INNER JOIN")
            .Append(" dbo.D_FeedbackText ON dbo.D_Feedback.Feedback_ID = dbo.D_FeedbackText.FeedbackText_FeedbackID INNER JOIN")
            .Append(" dbo.V_Case ON dbo.D_Feedback.Feedback_CaseID = dbo.V_Case.Case_ID INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.V_Case.Case_ConSaleID = dbo.L_Consultant.Con_ID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.V_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID WHERE")
            .Append(Me.ReportCriteria_Dates("D_Feedback.Feedback_DTEntered "))
            .Append(" AND (dbo.V_Case.Case_StatusID = 1030) ORDER BY dbo.D_Feedback.Feedback_DTEntered DESC")
        End With
        Return SQL.ToString
    End Function

    Private Function SQL_CasesAssigned() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append(" SELECT     TOP 100 PERCENT dbo.D_Case.Case_ID, dbo.L_ProductTypes.ProductType_Name, dbo.L_Products.Product_Name AS Product, ")
            .Append(" dbo.L_CaseStages.CaseStage_Title, dbo.L_Consultant.Con_Name AS CW")
            .Append(" FROM         dbo.D_Case INNER JOIN")
            .Append(" dbo.L_Consultant ON dbo.D_Case.Case_ConCWID = dbo.L_Consultant.Con_ID INNER JOIN")
            .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
            .Append(" dbo.L_CaseStages ON dbo.D_Case.Case_StatusID = dbo.L_CaseStages.CaseStage_Number INNER JOIN")
            .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID")


            'If Me.ReportCriteria Then

            Dim strDate As String = Me.ReportCriteria_Dates("dbo.D_Case.Case_DtEntered")
            If strDate.Trim <> "" Then
                .Append(" WHERE ")
                .Append(Me.ReportCriteria_Dates("dbo.D_Case.Case_DtEntered"))
            End If
            .Append(" GROUP BY dbo.L_Consultant.Con_ID, dbo.L_Consultant.Con_Name, dbo.L_Products.Product_Name, dbo.D_Case.Case_ID, dbo.L_CaseStages.CaseStage_Title, dbo.L_ProductTypes.ProductType_Name")
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(", dbo.D_Case.Case_ConSaleID   ")
                Dim strConSale As String = Me.ReportCriteria_Consultant("dbo.D_Case.case_consaleid", True)
                If strConSale.Trim <> "" Then
                    .Append(" Having ")
                    .Append(strConSale)
                End If
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(", dbo.D_Case.Case_ConCWID   ")
                Dim strConCW As String = Me.ReportCriteria_Consultant("dbo.D_Case.Case_ConCWID", True)
                If strConCW.Trim <> "" Then
                    .Append(" Having ")
                    .Append(strConCW)
                End If
            End If

            .Append(" ORDER BY dbo.L_Consultant.Con_ID, dbo.D_Case.Case_ID")
        End With

        Return SQL.ToString

    End Function
    Private Function SQL_LeadsPerConsultant() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select con_id,con_name,product_name,count(*) as numleads from d_case, l_consultant, l_products ")
            .Append("where d_case.case_enteredby = 'web' and d_case.case_consaleid = l_consultant.con_id and d_case.case_productid = l_products.product_id ")
            .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            If Me.ReportCriteria_Dates("case_dtentered") <> "" Then
                .Append(" and " & Me.ReportCriteria_Dates("case_dtentered"))
            End If
            .Append(" group by con_id,con_name,product_name")
        End With

        Return SQL.ToString

    End Function

    Private Function SQL_Commission() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("select c.case_id as payment_caseid, pay.payment_id,pay.payment_amount,c.case_productid,")
            .Append("c.case_companyid as companyid,pay.payment_type,pay.payment_dtentered,c.case_enteredby,")
            .Append("pay.payment_method,ch.defcharge_amount,ch.defcharge_tpcost,ch.defcharge_vat,")
            .Append("dbo.getcommission (ch.defcharge_amount,defcharge_tpcost,defcharge_vat,payment_amount) as commissionable,")
            .Append("dbo.personorcompany(c.case_companyid,c.case_candidateid) as name from d_case c,")
            .Append("a_payments pay, d_defcharges ch, l_products prod ")
            .Append("where c.case_id = pay.payment_caseid And c.case_productid = ch.defcharge_productid ")
            .Append("and c.case_companyid = ch.defcharge_companyid And prod.product_id = c.case_productid ")
            '.Append("and " & Me.ReportCriteria_Consultant("case_consaleid"))
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("case_concwid"))
            End If

            If Me.ReportCriteria_Dates("payment_dtentered") <> "" Then
                .Append(" and " & Me.ReportCriteria_Dates("payment_dtentered"))
            End If

        End With

        Return SQL.ToString

    End Function
    Private Function SQL_CashUPConsultant(ByVal caseid As Long) As String
        Dim SQL As New StringBuilder
        Dim ID As Long
        With SQL
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append("select case_consaleid from d_case where case_id =" & caseid)
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append("select case_concwid from d_case where case_id =" & caseid)
            End If
        End With
        ID = Data.GetScalar(SQL.ToString)
        If ID = 0 Then
            Return "UnSpecified"
        ElseIf ID > 0 Then
            Return Data.GetScalar("select con_name from l_consultant where con_id=" & ID)
        End If
    End Function

    Private Function SQL_CashUp() As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT  A_Payments.Payment_ID, CASE Payment_CompanyID WHEN 0 THEN '' ELSE Payment_CompanyID END AS CompanyID,")
            .Append("A_Payments.Payment_caseID, A_Payments.Payment_Type, A_Payments.Payment_Amount, A_Payments.Payment_DTentered,")
            '.Append("A_Payments.Payment_enteredBy, A_Payments.Payment_Method,")
            .Append(" A_Payments.Payment_Method,")
            .Append("CASE Payment_CompanyID WHEN 0 THEN Person_FName + ' ' + Person_LName ELSE Company_Name END AS Name ")
            .Append("FROM ((A_Payments INNER JOIN D_Case ON A_Payments.Payment_CaseID = D_Case.Case_ID) LEFT JOIN D_Company ")
            .Append("ON D_Case.Case_CompanyID = D_Company.Company_ID) LEFT JOIN D_Person ON D_Case.Case_CandidateID = D_Person.Person_ID ")
            .Append("WHERE " & Me.ReportCriteria_Dates("A_Payments.Payment_Dtentered"))
            '.Append(Me.ReportCriteria_Consultant("case_consaleid"))
            If Me.Criteria.Consultant_Role = ConsultantRoles.SoldBy Then
                .Append(Me.ReportCriteria_Consultant("case_consaleid"))
            ElseIf Me.Criteria.Consultant_Role = ConsultantRoles.CaseWorker Then
                .Append(Me.ReportCriteria_Consultant("case_concwid"))
            End If

            .Append(Me.ReportCriteria_Product("case_productid"))
            .Append(Me.ReportCriteria_SLA_Accounts)
            .Append(Me.ReportCriteria_Client_Types)
            .Append(" AND (NOT (dbo.A_Payments.Payment_Type LIKE 'Refund'))AND (NOT (dbo.A_Payments.Payment_Type LIKE 'Charge Back'))")
            .Append(" ORDER BY CASE Payment_CompanyID WHEN 0 THEN '' ELSE Payment_CompanyID END, A_Payments.Payment_DTEntered")

        End With

        Return SQL.ToString

    End Function

    Private ReadOnly Property Criteria() As ReportCriteria
        Get
            Return CType(My.Settings.ReportCriteria, Reports.ReportCriteria)
        End Get
    End Property

    Private Function ReportCriteria_Client_Types() As String

        Dim SQL As String = ""

        With Me.Criteria

            Select Case .Client_Type

                Case ClientTypes.All
                    'Do nothing

                Case ClientTypes.CorporateOnly

                    SQL = "Case_CompanyID > 0 "

                Case ClientTypes.PrivateOnly

                    SQL = "Case_CompanyID = 0 "

            End Select

        End With

        If SQL.Length > 0 Then
            SQL = " and " & SQL
        End If

        Return SQL

    End Function

    Private Function ReportCriteria_SLA_Accounts() As String

        Dim SQL As String = ""

        With Me.Criteria
            Select Case .Account_Types

                Case AccountTypes.All
                    'Do nothing

                Case AccountTypes.SLA

                    SQL = "Company_AccountType = 2 "

                Case AccountTypes.ExcludeSLA
                    SQL = "(Company_AccountType <> 2 OR Case_CompanyID = 0) "

            End Select
        End With

        If SQL.Length > 0 Then
            SQL = " and " & SQL
        End If

        Return SQL


    End Function

    Private Function ReportCriteria_Dates(ByVal DateCol As String) As String

        Dim SQL As String = ""

        With Me.Criteria

            If IsDate(.StartDate) And IsDate(.EndDate) Then
                SQL = " " & DateCol & " between '" & .StartDate & "' and '" & .EndDate & "'"
            End If

        End With

        Return SQL

    End Function
    Private Function ReportCriteria_DatesWeekly(ByVal DateCol As String) As String

        Dim SQL As String = ""
        Dim StartDate As String
        Dim EndDate As String

        StartDate = CStr(Format(CDate(Now.AddDays(-Weekday(Now, DayOfWeek.Monday))), "dd-MMM-yyyy")) & " 00:00:00"
        EndDate = CStr(Format(CDate(Now.AddDays(-Weekday(Now, DayOfWeek.Monday))).AddDays(6), "dd-MMM-yyyy")) & " 00:00:00" '(Format(CDate(EndDate), "dd-MMM-yyyy")) & " 23:59:59"
        If IsDate(StartDate) And IsDate(EndDate) Then
            SQL = " " & DateCol & " between '" & StartDate & "' and '" & EndDate & "'"
        End If

        Return SQL

    End Function
    Private Function ReportCriteria_DatesMonthly(ByVal DateCol As String) As String

        Dim SQL As String = ""
        Dim StartDate As String
        Dim EndDate As String

        StartDate = "01-" & MonthName(Now.Month, True) & "-" & Now.Year & " 00:00:00"
        EndDate = Format(CDate(Now), "dd-MMM-yyyy") & " 23:59:59"
        If IsDate(StartDate) And IsDate(EndDate) Then
            SQL = " " & DateCol & " between '" & StartDate & "' and '" & EndDate & "'"
        End If

        Return SQL

    End Function
    Private Function ReportCriteria_Product(ByVal ProductCol As String) As String

        Dim SQL As String = ""

        Select Case Me.Criteria.Product_Selection

            Case ProductSelection.All
                'No SQL needed

            Case ProductSelection.By_Department

                'Need to keep SQL as simple as possible as this function is reused for many queries

                Dim Str As New StringBuilder
                Dim Ids As New DataTable
                Dim IDList As String = ""
                Dim Row As DataRow

                With Str
                    .Append("SELECT L_Products.Product_ID")
                    .Append(" FROM L_Products INNER JOIN")
                    .Append(" L_ProductTypes ON L_Products.Product_Type = L_ProductTypes.ProductType_ID")
                    .Append(" WHERE L_ProductTypes.ProductType_DepartmentID = " & Me.Criteria.Product_Selection_Id)
                End With

                Ids = Data.GetQueryResults(Str.ToString)

                For Each Row In Ids.Rows

                    If IDList.Length > 0 Then
                        IDList = IDList & ","
                    End If

                    IDList = IDList & Row.Item("product_id")

                Next

                If IDList.Length > 0 Then

                    SQL = " " & ProductCol & " in (" & IDList & ")"
                End If

            Case ProductSelection.By_Product_Type

                Dim Str As New StringBuilder
                Dim IdList As String = ""
                Dim Ids As New DataTable
                Dim Row As DataRow

                With Str
                    .Append("Select Product_ID from L_Products where Product_Type IN (" & Me.Criteria.Product_Selection_Id & ")")
                End With

                Ids = Data.GetQueryResults(Str.ToString)

                For Each Row In Ids.Rows

                    If IdList.Length > 0 Then
                        IdList = IdList & ","
                    End If

                    IdList = IdList & Row.Item("product_id")

                Next

                SQL = " " & ProductCol & " in (" & IdList & ")"


        End Select

        If SQL.Length > 0 Then
            SQL = " and " & SQL
        End If

        Return SQL

    End Function

    Private Function ReportCriteria_Consultant(ByVal ConsultCol As String, Optional ByVal blnExcludeAND As Boolean = False) As String

        Dim SQL As String = ""

        With Me.Criteria

            If Not .Consultant_All Then

                If .Consultant_Id > 0 Then

                    'If .Consultant_Role = ConsultantRoles.SoldBy Then
                    '    SQL = " case_consaleid = " & Me.Criteria.Consultant_Id
                    'ElseIf .Consultant_Role = ConsultantRoles.CaseWorker Then
                    '    SQL = " case_concwid = " & Me.Criteria.Consultant_Id
                    'End If
                    SQL = SQL & " " & ConsultCol & " = " & Me.Criteria.Consultant_Id

                ElseIf .Consultant_BranchId > 0 Then

                    'If .Consultant_Role = ConsultantRoles.SoldBy Then
                    '    SQL = " case_consaleid in (" & Me.ConsultantsInDepartment(.Consultant_DeptId) & ")"
                    'ElseIf .Consultant_Role = ConsultantRoles.CaseWorker Then
                    '    SQL = " case_concwid in (" & Me.ConsultantsInDepartment(.Consultant_DeptId) & ")"
                    'End If
                    SQL = SQL & " " & ConsultCol & " in (" & Me.ConsultantsInBranch(.Consultant_BranchId) & ")"
                End If
            Else
                Dim consultantList As DataTable = Lookups.ConsultantListBranchWise(Environment.UserName)

                SQL = SQL & " " & ConsultCol & " in ("

                For currentRowIndex As Integer = 0 To consultantList.Rows.Count - 1
                    If currentRowIndex <> consultantList.Rows.Count - 1 Then
                        SQL = SQL & consultantList.Rows(currentRowIndex)(0) & ","
                    Else
                        SQL = SQL & consultantList.Rows(currentRowIndex)(0)
                    End If
                Next
                SQL = SQL & ")"
            End If

        End With

        If SQL.Length > 0 And blnExcludeAND = False Then
            SQL = " and " & SQL
        End If

        Return SQL


    End Function

    Private Function ConsultantsInDepartment(ByVal DepartmentId As Integer) As String

        Dim SQL As String
        Dim Ids As DataTable
        Dim Row As DataRow
        Dim IdList As String = ""

        SQL = "select con_ID from l_consultant where con_department = " & DepartmentId

        Ids = Data.GetQueryResults(SQL)

        For Each Row In Ids.Rows

            If IdList.Length > 0 Then
                IdList = IdList & ","
            End If

            IdList = IdList & Row.Item("con_id")

        Next

        Return IdList

    End Function


    Private Function ConsultantsInBranch(ByVal BranchID As Integer) As String

        Dim SQL As String
        Dim Ids As DataTable
        Dim Row As DataRow
        Dim IdList As String = ""

        SQL = "select con_ID from l_consultant where con_branchid = " & BranchID

        Ids = Data.GetQueryResults(SQL)

        For Each Row In Ids.Rows

            If IdList.Length > 0 Then
                IdList = IdList & ","
            End If

            IdList = IdList & Row.Item("con_id")

        Next

        Return IdList

    End Function

End Class
